INDEX+MATCH 関数の合わせ技の極意を知る
ファイルをダウンロード
▢ INDEX+MATCHで関数組合せの極意を知ることができる。
① INDEX関数+MATCH関数を組み合わせて使う数式
② VLookUp関数では不可能なことができるから
③ 組み合わせて使うというイメージがわかるから
⃞ 数式を作る場合の考え方(あくまで考え方なので“.Fauls”等は式から外しています)
① 最終的に何ができるのかを考える(図1)
・参照したいのか? 計算したいのか?
・この関数を大枠(一番左)に書く
② 引数の枠を考える(桜井の数値を調べる)
・INDEX( , ) とイメージする(図2)
・INDEX(A2:A5, ? )
(※最終から考えること)
・INDEX(A2:A5,3) (仮に3と仮定する)
※ 最後の3= MATCH(”桜井”,B2:B5)
(INDEXの時と同じように考える)
FILTER関数
▢ FILTER関数の書式(図1参照)
FILTER(配列,条件,空の場合)
↓
=FILTER(A2;C10,A2;A10=“田中”)
を入力すると、図2が表示される。
※ ProPlasのみの機能
世界一やさしい OFFSET関数の徹底解説
1.OFFSET関数
実務で役に立つ VLOOKUP関数応用 スゴ技①
ファイルをダウンロード
1.マスターにデーターが無い場合の対処法(図1)
① マスター側に広瀬という名前が無い場合の対処法
2.マスターの文字の最後に”␣”がある場合の対処法
② 図2のようにマスターの引数の文字に”␣”のような不要な文字がある場合。
③ 図3の通り&“*”を付ける。
“*”はワイルドカード(後に続く文字は何でもよい)
3.マスターが月で検索元が年月日(図4)
① マスターが月(書式で月が入っている)で検索元はyyyy/mm/dd形式の場合
② MONTHで月を抜き出す。
4.マスター側のデータが空欄の場合
① 図5のように名前に空欄があると、検索側に“0”が表示される場合。
② 図6の通り最後に &”” を付ける
5.マスターの文字と検索元の文字が違う①(図7)
① 検索元とマスターの文字が図7のように違う
・検索元:BCD-822(前と後ろを”-”でつないでいる)
・マスター:AB や ABC等文字数が違う
② LEFT関数とFIND関数の合わせ技で対応
6.マスターの文字と検索元の文字が違う②(図8)
① マスターの文字が図8の通り検索元の文字と違う
・検索側:100-QAZ(数字-文字)但し100は文字
・マスター側:100(数字)
・VALUE関数で文字の100を数字に変換
※ もっと簡単な方法
② 文字に“*1”を掛けて数字に変換
③ 文字に“+0”を足して数字に変換
練習問題 月の合計を求める ①
問題と解答をダウンロード
〔問題〕
A列にランダムな日付が200個あります。これらの日付とB列の数値から、D列とE列に月別の集計をしてください。なお、A列B列の表はテーブル形式になっています。
[解決策①]
〔解決策①のポイント〕
① A列B列をテーブルにする。
② D列の月に2020/1/1(シリアル値)を入れ、表示形式で1月を出す。
③ SUMIFS関数、IF関数、EOMONTH関数の合わせ技を使う。
[解決策②]
〔解決策(2)のポイント〕
① A列B列をテーブルにする
② E列の数式を配列数式にする
③ E列の数式={=SUM(IF(MONTH(Data2[日付])=D2,Data2[数値],0))}
[解決策③]
[解決策③]のポイント〕
① A列B列をテーブルにする
② ピボットテーブルを使う。
③ ピボットの表示位置を「既存のワークシート」 開始位置をD1にする。
Excelの上級者はROW関数を使っている
問題と解答をダウンロード
ROW関数の活用①
▢ A-001-01の“001”の部分を連続にしたい。(図1)
① =“A-”&TEXT(ROW(),”000”)&”-01”
② チェック欄の〇で挟まれた間の数値を合計(図2)
ROW関数の活用②
▢ AB列をテーブル化
① C列で〇の行番号を取得
② F列で最小値(F1)、最大値(F2)を求める
③ 最小値、最大値からアドレスを作成(F3)
④ アドレスを使い合計を出す。(INDIRECT関数を活用)
ROW関数の活用③
▢ チェック欄の〇で挟まれた間の数値を合計する
① AB列をテーブル化
② C列で〇の行番号を取得
③ C列の最小-最大の数値の合計をF4に表示
あまり知られていない TEXT関数が実務では大活躍する
ファイルをダウンロード
1.文字列結合の技
2.曜日を入れる技
3.数字を文字列形式に変更する技
IFS関数、SWITCH関数の違いとポイント
ファイルをダウンロード
1.IFS関数、SWITCH関数のイメージ
2.IFS関数、SWITCH関数の引数と考え方
3.IFS関数と、SWICH関数の違い
① IFS・・・複数のもの(セルや数式)を判定できるもの
② SWITCH・・・判定できるものは1つのもの(セルや数式)
③ IFS・・・「大きい」、「小さい」、「ではない」などで判定ができる
④ SWICH・・・「等しい」しか判定できない
⑤ どれにも該当しなかったら
コメント