全3件 (3件中 1-3件目)
1
Excelで表の中から条件に一致する特定の値を参照したいとき、VLOOKUP関数HLOOKUP関数INDEX関数+MATCH関数XLOOKUP関数(2020年以降なら)などを使う方法がある。これらの関数を駆使すれば、表の中から条件に一致する特定の値を持ってきたいとき大抵の場合上手くいくのだが、この間仕事で詰まったことがある。その時やりたかったことが、・IDが一致する・日付が「~以上」である・日付が「~以下」であるという3つの条件に一致する行の値を抽出したいというもの。上で挙げた関数たちは、基本的にただ条件に一致するものを持ってくるのは得意なのだが、「~以上」とか「~以外」とかの条件を指定しようとするとちょっとテクニックが必要となる。しかも今回はそれを「~以上」と「~以下」で複数条件・・・複数条件となると、私の知っているちょっとしたテクニックでもうまくいかない・・・参照元の表に計算用の列をいくつも用意すればそれも実現できるのだが、保守性の面でそれはやりたくなかった。そういうとき使える方法をその時頑張ってネットで検索したのだが、いい方法が見つからず・・・結局自分なりにやり方を見つけたので、忘れないようここに残しておく。概要例として上記のような表が存在するとする。各クラスの月ごとの日直担当を管理している表ですね。この日直管理表から、↑の結果表のK4セルのように・クラス:2-1・日付:2023/2/1・性別:女の日直の名前を抽出したい場合、以下のような計算式となる。{=INDEX($F$9:$F$33,MATCH(1,($B$9:$B$33=$H4)*($C$9:$C$33<=$I4)*($D$9:$D$33>=$I4)*($E$9:$E$33=$J4),0),1)}計算式を要約するとこんな感じ。{=INDEX(日直名列,MATCH(1,(クラス列="2-1")*(開始日列<="2023/2/1")*(終了日列>="2023/2/1")*(性別列="女"),0),1)}最初と後ろに{}がついているのは、配列数式という方法を使っているため。(配列数式がわからない人は、他のサイトで先に調べたほうがいいかもしれません。)◆注意配列数式を使っているため、この計算式をセルに入力後は、必ずCtrl+Shift+Enterキーで確定する必要があります。※:ただしExcel2019以降のバージョンなら、このキーで確定する必要はなしこのExcel計算式は、大きく3つに分けられる。 ①:($B$9:$B$33=$H4)*($C$9:$C$33<=$I4)*($D$9:$D$33>=$I4)*($E$9:$E$33=$J4)②:MATCH(1,【①の結果】,0)③:=INDEX($F$9:$F$33,【②の結果】,1)①の計算式は、表内を検索するときの条件②の計算式は、①と③の計算式を繋ぐためのもの③の計算式は、最終的にセルに表示したい値の範囲を設定するちなみに=INDEX($F:$F,MATCH(1,($B:$B=$H4)*($C:$C=$I4)*($E:$E=$J4),0),1)のように列ごと範囲を指定しても、(このエクセルの場合)結果は同じとなる。逆に=INDEX($F$9:$F$33,MATCH(1,($B$10:$B$33=$H4)*($C$9:$C$32=$I4)*($E$9:$E$33=$J4),0),1)↑の赤字の箇所のように、各箇所で指定している範囲の高さが異なると、結果がおかしくなるため注意。列ごと範囲を指定してもなぜうまくいくのか、各箇所で範囲の高さが異なるとなぜおかしくなるのかは、この後の説明を見ればわかる・・・はず!①の計算式($B$9:$B$33=$H4)*($C$9:$C$33<=$I4)*($D$9:$D$33>=$I4)*($E$9:$E$33=$J4)↓要約(クラス列="2-1")*(開始日列<="2023/2/1")*(終了日列>="2023/2/1")*(性別列="女")この計算式はさらに①-A:$B$9:$B$33=$H4 (B9:B33の範囲内の値が、H4セルの値に一致するか)①-B:$C$9:$C$33<=$I4 (C9:C33の範囲内の値が、I4セルの値以下か)①-C:$D$9:$D$33>=$I4 (D9:D33の範囲内の値が、I4セルの値以上か)①-D:$E$9:$E$33=$J4 (E9:E33の範囲内の値が、J4セルの値に一致するか)の4つの条件に分けられる。(抽出したい条件を変えたい場合、これが3つになったり6つになったりする。)今回の例では、一致/以上/以下のパターンのみ扱っているが、当然①-X:$B$9:$B$33<>$H4という不等号(<>)を使った計算式にすれば、 (B9:B33の範囲内の値が、H4セルの値と不一致か)という条件になる。この計算式をそれぞれ()で囲んで、それぞれを乗算(*)すると、指定した全ての条件に一致する場合1を返すという計算式になる。なぜ乗算するのか?乗算するとどうなるのか?については、・Excelでは「A=B」のような条件式を書いた場合、 条件が一致していれば「TRUE」、 条件が不一致であれば「FALSE」を返す・Excelでは計算するとき、 「TRUE」は「1」として、 「FALSE」は「0」として扱われるという2つのルールをわかっていれば、何をしたいのか見えてくる。①の計算過程を表で説明すると以下のような感じ。①の計算結果:{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;1;0;0;0;0;0;0;0;0}②の計算式MATCH(1,【①の結果】,0)MATCH関数は、第2引数で指定した範囲内から、第1引数に一致するセルの相対的な位置を返す。今回の計算式の場合、第1引数が1第2引数が【①の結果】のため、配列数式で返ってきた①の計算結果内から、1に一致するセルが何番目なのかを返してくれる。ちなみに1に一致するセルが複数ある場合、一番最初に見つかったセルを返す。②の計算過程を表で説明すると以下のような感じ。②の計算結果:16③の計算式=INDEX($F$9:$F$33,【②の結果】,1)↓要約=INDEX(日直名列,【②の結果】,1)INDEX関数は、第1引数で指定した範囲内から、[第2引数で指定した値]行目のセルの結果を返す。(第3引数は何列目かを指定するが、今回は1行だけの範囲を指定しているため1固定)今回の場合、第1引数の範囲がF9:F33なので、第2引数が1ならF9の値第2引数が2ならF10の値第2引数が3ならF11の値~~~第2引数が24ならF32の値第2引数が25ならF33の値って感じで返してくれる。今回の例の場合、【②の結果】が16のため、以下のように上から16番目のF24の値を返す。③の計算結果:"花子16代目"応用編:OR検索したい場合ここまでは全てAND条件の検索だったが、ここでさらにOR検索をしたい場合、以下のような計算式になる。=INDEX($G$9:$G$33,MATCH(1,IF((($B$9:$B$33=$I7)*($C$9:$C$33<=$J7)*($D$9:$D$33>=$J7)*($E$9:$E$33=$K7))+(($B$9:$B$33=$I7)*($C$9:$C$33<=$J7)*($D$9:$D$33>=$J7)*($F$9:$F$33=$L7)),1),0),1)※長いので改行してます実際の検索例でいうと以下のような感じ。先に説明したANDだけの検索計算式と異なるのは、=INDEX($G$9:$G$33,MATCH(1,IF((($B$9:$B$33=$I7)*($C$9:$C$33<=$J7)*($D$9:$D$33>=$J7)*($E$9:$E$33=$K7))+(($B$9:$B$33=$I7)*($C$9:$C$33<=$J7)*($D$9:$D$33>=$J7)*($F$9:$F$33=$L7)),1),0),1)の太字の部分。上の赤い条件式を【条件A】青い条件式を【条件B】だとすると、【条件A】 or 【条件B】といった条件になる。条件同士を()で囲んで+してあげればor条件での検索は完璧!と言いたいところなのだが・・・見ての通りこの計算式では2つの条件式の結果を足しているので、両方の結果がTRUEになる場合、結果が2となってしまい、そのままMATCH関数に渡すとうまく動かない。なので、【条件A】+【条件B】をIF(【条件A】+【条件B】,1)のようにIF文で囲ってあげると、条件の結果を加算した結果が1以上の場合1を返してくれるようになる。これはorにしたい条件が増えてIF(【条件A】+【条件B】+【条件C】,1)となった場合も同様である。ただ当然のごとく、条件を増やせば増やす程どんどん重くなるので取扱い注意!デメリットVLOOKUP関数HLOOKUP関数INDEX関数+MATCH関数XLOOKUP関数(2020年以降なら)この方法が上記の方法より明確に劣っている部分を説明する。デメリット①「~を含む文字」といったワイルドカード検索ができないMATCH関数やLOOKUP関数を使った条件指定なら、「花子*」って感じで条件を指定してあげれば、「花子」から始まる文字列を検索してくれるが、今回の方法ではそれが使えない。もし名前が「花子」から始まって、2020/1/1 ~ 2020/1/31の期間で日直を担当した人て感じで調べたい場合は、諦めて計算用の列を別に用意するか、VBAマクロを使って集計したほうが良いかと思います。デメリット②重い 遅いとりあえずこれが1番のデメリット。データ量が大したことなければサクサク動くが、この計算式が大量に使われているExcelを開くと重くなるし、計算にも時間がかかる。もちろんそれは他の計算式にも言えることですが、、、やはりVLOOKUPなどの標準のExcel関数に比べれば重い。まず今回使っている配列数式という仕組みそのものが重い。今回説明したこれ以外にもいくつか配列数式を使う方法は考えたが、他はさらに重かった。データ量の多い表に対してこの計算式を使うなら、Excelの自動計算の設定をOFFにするのが賢明でしょう。もしくはやっぱり、デメリット①と同じくVBAマクロを使うかですね。この方法は自己流の方法で、正直全然自信が無いため、「もっといい方法があるよ」「ここがどういう意味かわからない」「こういうときに役に立ったよ」という方がいらっしゃったら是非是非コメント頂けると有り難いですm(_ _)m
2023/10/17
コメント(0)
WinMergeというテキストファイル比較に便利なソフトがありますが、このソフトは比較した2つのテキストの内容が同一だと、デフォルトで「選択されたファイルは同一です。」というメッセージダイアログを出してくれる。このダイアログボックス、出ないようにすることも可能で、そのときはこのダイアログボックスにある「再びこのメッセージを表示しない」のチェックボックスにチェックを入れてOKを押せば、それ以降はファイルが同一でもこのメッセージダイアログが表示されなくなるのだが、、、一度このメッセージを表示させなくすると、設定画面を探しても再び表示させるようにする設定が見つからない・・・もしかしたら私が見つけられないだけで、もっと頑張って探せばその設定もどこかにあるのかもしれないが、いくら探しても、ネットで情報を調べても、そんな方法は見つからない(or 情報が古い)。昔は設定画面から「リセット」ボタンを押せばこのメッセージも復活したような気がするのだが、今のWinMergeのバージョンで「デフォルト」ボタンを押しても元に戻らず・・・仕方がないので自分自身へのメモのためにも、「選択されたファイルは同一です。」メッセージダイアログを再表示させる方法をここに残しておく。①WinMergeの設定画面を開く。②設定画面から現状の設定をエクスポート。③保存した設定ファイル(iniファイル)をテキストエディタで開く。一度ダイアログを非表示にしてしまったWinMergeの設定ファイルならMessageBoxes/42101=1という設定値がiniファイル内に存在するはず。④iniファイル内の設定値を書き換える。設定ファイル内のMessageBoxes/42101=1の設定値をMessageBoxes/42101=0に修正。ちなみに「MessageBoxes/42101」の行を消しただけでは駄目。ちゃんと0に書き換えること。⑤WinMergeの設定画面に戻り、先程書き換えた設定ファイルをインポート。結果これで再び「選択されたファイルは同一です。」のメッセージダイアログが表示されるようになりました。めでたしめでたし。
2022/04/23
コメント(0)
うわーーん(´д`;) マビブロガーの集いにいってから、巡回するブログが増えすぎて更新チェックしきれないよ;;せっかく知り合えたみんなのブログなのに、時間がないからって見られないのは嫌だ~orz川 フフフ~、お困りのようだね。コオシン君~ あっ、きっ・・・君は! 富えもん!! ボークー、トーミーエーモーン!! ふふふ、お困りのようだね。コオシンくん。 ブログの更新チェックと確認に時間がかかるのかい? ふーふーふー、そんな時はね。ハイ! RSSリーダー Headline-Reader Liteーー!! わぁーーーい、富えもんのポケットからブログラムがーーーー\(゚∀゚)/このソフトどうやって使うの?富えもん。 ふふふ~、じゃあ使い方を教えてあげるよ~まず画面左上のヘッドラインを右クリックして、メニューから【グループの追加】を選択するんだ。 そうすると新しいグループができるから、適当な名前を入力!マビノギのブログのグループを作りたいなら、名前は【マビノギ】とかにしておけば良いと思うよ~ その後、追加したいグループを選んだ状態でメニュー左上の【追加】ボタンをクリックだ! そうすると、アドレスを入れる画面が出てくるよね~ここにそのブログのRSSのアドレスを入れて【実行】を押せば追加は完了さ― 富えもーん!RSSのアドレスって何?そのブログのアドレスじゃ駄目なの?どうやって調べればいいのかわかんないよーー\(`Д´)/ RSSのアドレスがわからないのか~仕方ないな~コオシン君は。 PSSのアドレスの調べ方はブログによって色々あるんだけど、このソフトを使うなら、RSSアドレスを調べる手間も省けるよ~ 画面真ん中辺りに、アドレスを入れる欄があるよね~そこに見たいブログのアドレスを入れて、右端の【→】ボタンをクリックだ― そうすると、下の画面が見たいブログに移動するよね~ その状態でさっきみたいに、 追加ボタンをクリックすると~・・・ わ~、最初からRSSアドレスっぽいのが入ってる! やったね♪ コオシン君これで【実行】ボタンを押せば、ブログの追加は完了さ! これであとは【更新】ボタンさえ押せば、それだけでそのブログの新着記事がわかるよ! わー\(゚∀゚)/有難う!富えもん!これで巡回するブログをあと200~300個増やしても大丈夫だね!! ほっ、ほどほどにね・・・コオシン君・・・
2013/04/07
コメント(8)
全3件 (3件中 1-3件目)
1