Excelが大好きだ!

Excel大好き経理マンがExcelの事を書き綴っていきます。


スポンサードリンク

関数

Timevalueは一日未満

時間の計算ってあまりやらないうえに色々とトラップがあるので、余計な時間がかかることがあります。 今日はTimevalue関数で起きたトラップ(自分が知らんだけ)をご紹介。 1日以上増えない 1日未満のみ VALUE関数 1日以上増えない こんな計算をしたんですが、…

COUNTIF関数は数値に見える文字列は数値で条件一致判定を行う

先日仕事でデータの重複チェックを行おうと思ってCOUNTIF関数でやってみようと思ったら、思いがけない結果にハマってしまったので備忘録。 数値で検索? 解決法 シングルクォーテーションを追加 別の関数 COUNTIF関数を躾ける まとめ 数値で検索? 下記の事…

正規表現検索が出来るユーザー定義関数を作った

エクセルの神髄さんのExcelクイズをきっかけに発生したやりとりをヒントに、正規表現検索が出来るユーザー定義関数を作成してみました。 「エクセル頭の体操」実務編コメントから特定形式「(yyyy年mm月)」の年月だけを取り出してください。 pic.twitter.co…

ユーザー定義関数をセルからは参照できないようにする(VBEからは参照できる)方法

先日、ユーザー定義関数を作成してモヤモヤしたことをつぶやいたところ様々な貴重なご意見を頂いたので備忘録。 ユーザー定義関数を別モジュールからは呼べるけど、セルからは呼べない状態にすることって出来ましたっけ?— Kou Excelが大好きだ! (@LoverExc…

配列定数に変数を差し込む方法

以前に下記のExcelクイズに解答したことがある。 高校以降では連立方程式を解くのに行列を使います。これを応用しているのがKouさんの回答です。https://t.co/hccsHw5b8o定数配列の一部に変数を差し込むというテクニックも有用そうです。— 遊牧家族/yuuboku …

R1C1形式でも絶対参照が出来るんです

先日、別の人が作ったマクロの調整依頼を受けてコードを眺めていたときに見慣れないコードがあって、どゆこと?と思ったら実は目から鱗だったお話。 R1C1形式 R1C1形式の絶対参照 R1C1形式 R1C1形式はRに行方向、Cに列方向の位置を指定してセルを指定する方…

Excelでガントチャートを作ってみた

これから年度末決算に向かっていくほど、毎年のことながらタスクがガンガンに増えてくる。 ただそれだけのことなら毎年のことなのでやることはわかっているので予め段取り組んで対策することが出来る。 しかし、今年はそれに追い打ちをかけるようにイレギュ…

MID関数とSEAQUENCE関数で縦書き文章を作成

猫も杓子もエクセルクイズである。 今回は風柳@furyuteiさんが出題されていたこちらからの学びを備忘したいと思います。 #エクセルクイズ #スピル #54字の物語入力欄(R4)に入れた文字列の先頭54字(改行は空白に置換して1文字と数える・サロゲートペア等は考…

指定範囲の連番を指定回数返すもっとシンプルな数式

先日このような記事を公開したところ、twitterフォロワーの風柳@furyuteiさんから超速で別解を頂いた。 www.excellovers.com しかもこれが、自分が作成した数式よりめちゃくちゃシンプル。 あの短時間でさらっとこれが出てくるのが恐ろしい。 もっとシンプル…

指定範囲の連番を指定回数返す数式

以前にはけたさんのExcelクイズを考えている際に不意に出来上がってしまった数式を備忘録として残す。 繰り返し連番を作成する数式 =SEQUENCE(連番*繰返回数,,初期値)-(INT((SEQUENCE(連番*繰返回数,,初期値)-初期値)/連番)*連番) 使用例 もっと簡単な表現も…

SQLでデータを抽出するユーザー定義関数を作ってみた その2

先日SQLでデータを抽出するユーザー定義関数SQLSearchを作成した。 www.excellovers.com その後テストした際に追加したいと思った機能を追加してみた。 その際に風柳さん@furyutei には貴重なアドバイスを頂きました。 ありがとうございます。 例えば ADODB.…

SQLでデータを抽出するユーザー定義関数を作ってみた

Office365やExcel2019で新しく搭載された「スピル」と言う機能があります。 スピル機能を使うと複数の答えを返す関数を従来よりも圧倒的に簡単にセルに展開することが出来ます。 この機能と相性が良いのでは思っていた、ExcelのデータをSQL文で集計した結果…

未入力は計算上は0

フリもなく早速検証です。 ​ B2~B4セルは未入力 B5セルは 半角スペース B6セルは =”” という式が入力されています。 表を見ると未入力は 0 として計算されています。 一方空白の2種類は0ではなく文字列として認識されているので計算でエラーが発生してい…

テーブルの列名参照を含む式をオートフィルした時に列がずれる現象への対応方法

テーブル機能便利ですよね。 Excel2007以降での最高の追加機能だと思います。 しかし先日このような場面に出くわした。 ※データは例のごとくなんちゃって個人情報より テーブルの列名参照は名前定義みたいなもので絶対参照だと思いこんでいたので、このよう…

sumproduct関数でのOR条件の指定

前回はsumifs関数でのOR条件の指定方法を紹介した。 www.excellovers.com 今回はみんな大好きsumproduct関数をsumifsの代替で使用する際のOR条件の指定方法を紹介します。 ※大好きだよね? OR条件公式 前回と同じ事例に対して3つの方法を作ってみた。 まず…

マイナスの割り算の答え(商)

先日このような記事を公開した。 www.excellovers.com その際にちょっと気になったことがあった。 Excelに商(割算の答え)を算出する QUOTIENTという関数があるのだが、この関数の返り値がちょっと疑問符がついてしまった。 Quotient関数 QUOTIENT (分子,分…

文字列を区切り文字で区切って配列化する関数

ここ最近twitterで #エクセルクイズというタグで出題されている問題に色々とチャレンジしてきました。 その中で配列数式を使用することも多かったのですが、自分の思いもよらない使い方をされているのを見かけて勉強になりました。 今回はその集大成として文…

オートフィルタで見えている範囲(可視セル)だけで自動連番を作る方法

先日Twitterでこのようお題が出されていました。 フィルターを設定した表について、表示されている行のみに通し番号を振ってください。・フィルターの条件を変更すると番号も変わります。・式はA2セルに入力し、下方にコピーしてください。#エクセルクイズ p…

単一セル内でドロップダウンリストを多重に連動させる方法

先日このようなツイを見かけた。 Excelクラスタの皆さん、単一セル内でドロップダウンリストを多重に連動させる方法分かりますか…これ当社で作った人いて、スゲーてなってるんですけど既にその人退職して行方知れずに…— しいこふは現実化する (@i_siecof) Ma…

配列のセルへの一括転記(3.転記の方法と大量データの並び替え)

前々回で配列の並び方、前回で行列の並び替えについて調べてみました。 www.excellovers.com www.excellovers.com 今回は配列のセルへの一括転記とTranspose関数の限界を超える大量データの並び替えについて調べてみました。 Transpose関数の限界と対応 配列…

配列のセルへの一括転記(2.配列の行列並び替え)

前回は配列のデータの並び方について調べました。 www.excellovers.com 今回は前回解説した3種類の配列について、それぞれ行列並び替えをした際に どのようにデータ並び替わるのかを調べてみたいと思います。 Transpose関数で行列並び替え VBAで配列の行列…

Text関数の「0埋め」についてふと思ったこと

TEXT関数、ちょいちょい使います。 といっても僕の使用用途で最も多いのは 「0埋め」です。 例えば本当は5桁のデータだけど、 取り込んでいるデータでは頭の0部分が欠落している場合に 0を補填する場合です。 で、ふと思ったんですけど これ数字だから前…

VBAでの改行の方法

VBAでの改行の方法 メッセージボックス(msgbox)で表示する文章が長くなってくると、見やすくするために改行を行いたときがあります。 VBAには改行を表現が何種類か用意されています。 それぞれの違いを交えながら紹介したいと思います。 改行表現一覧 定数 …

恐怖!計算式の答えが必ず0に!(循環参照の見つけ方

先日ある計算式を作ったんですよ。 (※実際のややこしい表の一部抜粋のため数式もややこしい) そしたらですね、数式はあってるのに(あってないけど) 答えが0しか表示されないんですよ。 どこがおかしいんだろうと思って、「数式の検証」で調べてみると 途中…

空白の消し方

本日のご質問。 vlookup関数でちゃんと入力しているつもりなんだけど答えが正しく表示されません。 Vlookupが上手くいかない これは結論から言うと、参照するデータのコードの後ろに余白が入力されているため一致しなかったんですね。 別のシステムから出力…

月末日付の求め方・EOMONTH 関数

月末日付の求め方 請求書の締切日などを記載するために月末の日付を算出しないといけないことは多々あると思われます。 今までは次のような数式を使って求めていました。 = Date(2017,8+1,1)-1 これは求めたい月末の翌日1日を作成してから1日マイナスして…

関数で数式を文字列として検索する方法

こんな感じの表を頂きました。 この表には期首残の項目が無く、期末残の数式の中に埋め込まれているという恐ろしい作りになっています。 期首残の数値が必要だったため数式から抜き出そうと思いMid関数と Find関数 で抜き出そうとしたのですが、 Find関数で…

ピボットテーブルで項目が重複して表示される

本日のご質問。 ピボットテーブルで同じ項目が複数表示されます。 どうしたら1つにまとめられますか? 別れている理由わかりますか。 先程の画像だとわかりにくいですけど、フォントをMS 明朝に変えたコチラだとどうでしょう。 全角・半角で別文字扱い 答え…

VLookupの列指定

本日頂いたご質問。 こんな表で店Noを入力したときに、グループマスターから対応するグループコードを表示したい。 このような場合に真っ先に思いつくのはVLOOKUP関数です。 しかし残念ながら今回の事例では使えません。 列指定は右側だけ VLOOKUPの引数は以…

Sumproduct関数を利用した複数条件の合計算出法

Excel2003の使用期間が長かったため複数条件の合計を出したいときは Sumproduct関数を使ってしまいます。 こんな感じ この例だと「日付」列を対象にして月が1、日が1の金額を合計する。 このように検索条件が複数ある場合の合計は問題なく出来るのだけど、…