Excel 関数編

INDEX+MATCH 関数の合わせ技の極意を知る

ファイルをダウンロード

▢ INDEX+MATCHで関数組合せの極意を知ることができる。
 ① INDEX関数+MATCH関数を組み合わせて使う数式
 ② VLookUp関数では不可能なことができるから
 ③ 組み合わせて使うというイメージがわかるから

⃞ 数式を作る場合の考え方(あくまで考え方なので“.Fauls”等は式から外しています)

 ① 最終的に何ができるのかを考える(図1)
    ・参照したいのか? 計算したいのか?
    ・この関数を大枠(一番左)に書く

画像に alt 属性が指定されていません。ファイル名: image-24.png

 ② 引数の枠を考える(桜井の数値を調べる)
    ・INDEX(   ,   ) とイメージする(図2)
    ・INDEX(A2:A5, ?  )
   (※最終から考えること)
    ・INDEX(A2:A5,3)   (仮に3と仮定する)
    ※ 最後の3= MATCH(”桜井”,B2:B5)
      (INDEXの時と同じように考える)

画像に alt 属性が指定されていません。ファイル名: image-25.png

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・・・「等しい」しか判定できない
  ⑤ どれにも該当しなかったら

コメントを残す

メールアドレスが公開されることはありません。