カテゴリー: ExcelTech

Ｅｘｃｅｌ 関数編

0

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

ファイルをダウンロード

関数編_１ダウンロード

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

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

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

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

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

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

FILTER関数

▢　FILTER関数の書式（図1参照）
　　 FILTER(配列,条件,空の場合)
　　　　　　　↓
　　＝FILTER(A2;C10,A2;A10=“田中”)
　　を入力すると、図2が表示される。
　　※　ProPlasのみの機能

世界一やさしい　OFFSET関数の徹底解説　

１．OFFSET関数

実務で役に立つ　VLOOKUP関数応用　スゴ技①

ファイルをダウンロード

関数編_１ダウンロード

１．マスターにデーターが無い場合の対処法（図1）

　　①　マスター側に広瀬という名前が無い場合の対処法

２．マスターの文字の最後に”␣”がある場合の対処法

　②　図2のようにマスターの引数の文字に”␣”のような不要な文字がある場合。
　③　図3の通り＆“*”を付ける。
　　　“*”はワイルドカード（後に続く文字は何でもよい）

３．マスターが月で検索元が年月日（図4）

　①　マスターが月(書式で月が入っている)で 検索元はyyyy/mm/dd形式の場合
　②　MONTHで月を抜き出す。

４．マスター側のデータが空欄の場合

　①　図5のように名前に空欄があると、検索側に “0”が表示される場合。
　②　図6の通り最後に &”” を付ける

５．マスターの文字と検索元の文字が違う①（図７）

　①　検索元とマスターの文字が図7のように違う
　　　・検索元：BCD-822（前と後ろを”-”でつないでいる）
　　　・マスター：AB や ABC等文字数が違う
　②　LEFT関数とFIND関数の合わせ技で対応

６．マスターの文字と検索元の文字が違う②（図8）

　①　マスターの文字が図8の通り検索元の文字と違う
　　　・検索側：100-QAZ（数字-文字）但し100は文字
　　　・マスター側：100(数字)
　　　・VALUE関数で文字の100を数字に変換
　※　もっと簡単な方法
　②　文字に“*1”を掛けて数字に変換
　③　文字に“+0”を足して数字に変換

練習問題　月の合計を求める　①

問題と解答をダウンロード

F2020-04-15ダウンロード

〔問題〕
Ａ列にランダムな日付が200個あります。 これらの日付とＢ列の数値から、Ｄ列とＥ列に月別の集計をしてください。なお、Ａ列Ｂ列の表はテーブル形式になっています。

［解決策①］

〔解決策①のポイント〕
　　①　A列B列をテーブルにする。 
　　②　Ｄ列の月に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関数の活用ダウンロード

ROW関数の活用①

▢　A-001-01の“001”の部分を連続にしたい。(図1)
　　①　=“A-”&TEXT(ROW(),”000”)&”-01”
　　②　チェック欄の〇で挟まれた間の数値を合計(図2)

ROW関数の活用②

▢　ＡＢ列をテーブル化
　　①　C列で〇の行番号を取得
　　②　F列で最小値（F1）、最大値（F2）を求める
　　③　最小値、最大値からアドレスを作成（F3）
　　④　アドレスを使い合計を出す。(INDIRECT関数を活用)

ROW関数の活用③

▢　チェック欄の〇で挟まれた間の数値を合計する
　　①　AB列をテーブル化 　
　　②　Ｃ列で〇の行番号を取得 　
　　③　Ｃ列の最小－最大の数値の合計をＦ４に表示 　

あまり知られていない　TEXT関数が実務では大活躍する

ファイルをダウンロード

TEXT関数ダウンロード

１．文字列結合の技

２．曜日を入れる技

３．数字を文字列形式に変更する技

ＩＦＳ関数、ＳＷＩＴＣＨ関数の違いとポイント

ファイルをダウンロード

IFS_SWITCH関数ダウンロード

１．ＩＦＳ関数、ＳＷＩＴＣＨ関数のイメージ

２．ＩＦＳ関数、ＳＷＩＴＣＨ関数の引数と考え方

３．ＩＦＳ関数と、ＳＷＩＣＨ関数の違い
　　①　IFS・・・複数のもの（セルや数式）を判定できるもの
　　②　SWITCH・・・判定できるものは1つのもの（セルや数式）
　　③　IFS・・・「大きい」、「小さい」、「ではない」などで判定ができる
　　④　SWICH・・・「等しい」しか判定できない
　　⑤　どれにも該当しなかったら

Ｅｘｃｅｌ機能編

0
①Excelスゴ技_機能編ダウンロード

マウスの便利技　①

マウス関連ダウンロード

▢　右ボタンでセルの下をドラッグ
　　右の図1がポップアップ。動作を選択

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

▢　オートフィル　オプションボタン
　　フィルハンドルをドラッグすると、図2が表示。
　 動作を選択

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

▢　右ボタンでフィルハンドルをドラッグ
　　①　右ボタンでフィルハンドルをドラッグすると、図3が表示。
　　　　動作を選択　

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

マウスの便利技　②

マウス関連ダウンロード

▢　連続データ（加算）（乗算）
　　①　2つの連続データをフィルハンドルでドラッグすると、図5が表示。
　　②　連続（加算）・・・等差数列
　　③　連続（乗算）・・・等比数列

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

マウスの便利技　③

マウス関連ダウンロード

▢　連続（乗算）で予測をする。
　　①　始点～終点までをフィル右ハンドルでドラッグすると
　　　　図6が表示。

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

　　②　連続データを選択すると、図7が表示。

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

　　③　乗算、データ予測を選択し「OK」ボタンを
　　　　クリック図8が表示。
　　　　グラフを表示すると予測曲線がわかる。

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

マウスの便利技　④

マウス関連ダウンロード

▢　ダブルクリックでカーソル移動
　　①　図9の様にセルの角をダブルクリックすると、その方向の
　　　　先頭又は最終セルに飛ぶ。
　　②　表の枠外からCtrl+矢印キーでその表の一番近いセルに飛ぶ。

▢　マウスでデータの入れ替え
　　①　図10の様にセル範囲を選択し入替えしたい場所に
　　　　ドラッグし、Shiftを押しながら離すと、データの入替えが
　　　　できる。



▢　マウスでデータをクリア又は削除
　　①　図11の様にフィルハンドルを上にドラッグするとデータが
　　　　クリアされる(書式は残る)
　　②　フィルハンドルをCtrlを押しながらドラッグすると
　　　　データを削除できる（書式も消える）

データ入力時の書式の引継ぎ条件

　Ｅｘｃｅｌの表に入力するとき、上段に書式や計算式が設定してある場合に、それを引き継ぐ機能がありますが、実際には「引き継ぐ場合」と「引き継がない」場合があります。
そこで、その条件を下記に解説いたします。

下の条件2つを満たしたときに書式を引き継ぐ

セル内改行を消す

１．選択範囲のセル内改行を消す
　　①　検索と置換で消す（手入力で改行を入れた場合はこれでOK）
　　　　検索文字列＝Ctlr+J
　　　　置換後の文字列＝空白（何も入力しない）
　　②　Clean関数で消す（VBAで改行を入れた場合は①では消せない場合がある）(vbCrLf)
　　　　セルA1が対象の場合　・・・Clean(A1)　

キー操作だけでオートフィルタ―を絞り込む秘儀

１．選択した名前で絞り込む
　　①　名前を選択（図1）
　　②　右クリック（図2）（又は図3のボタン）
　　③　E、Vを押す。

　　または・・・
　　①　名前を選択
　　②　Shift＋F10　（右クリックと同じ）
　　③　E、Vを押す

２.オートフィルターの全解除
　　①　Ctrl＋Shift+L
３．オートフィルターの一部解除
　　①　Alt+↓
　　②　E

セルのモードを理解すればイライラは解消する

１．セルのモードは4つある。（）内はセルの状態　(図1)
　　①　準備準備完了(閉じている)
　　　　矢印キーを押すと、セルが移動
　　②　入力（開いている）
　　　　矢印キーを押すと、そのセルを閉じてセルを移動
　　③　編集（開いている）
　　　　矢印キーを押すと、セル内を移動
　　④　参照（開いている）
　　　　矢印キーを押すと、参照しているセル移動して
　　　　アドレスが変わる。

２．F2キーでイライラ解消　（図2）
　　①　＜例＞
　　　　データの入力規則でセル範囲を設定時などで、
　　　数式を編集したい時など、F2キーで「入力モード」と
　　　「編集モード」の切り替えができる。

Ｅｘｃｅｌの隠れた便利機能。選択オプション

重複データの削除

エクセル講座ダウンロード

１．表の中の任意の場所にカーソルを置く（表1）
２．「データ」ー「重複の削除」を選択（図1）
３．「重複の削除」ダイアログが表示される（図2）
４．「すべて選択」「OK」で重複データが削除される。

オートフィルターのウルトラテクニック

エクセル講座ダウンロード

オートフィルターは、2つ以上設定する場合（表1のAMとPM等）全てANDとなる。
これは、オートフィルターの仕様でありORの設定はできない。
そこで下のテクニックが必要となる。

意外と知らない検索テクニック

エクセル講座ダウンロード

「検索と置換」は「オプション」があり意外と知られていない機能がある。
そこで、それを下記に紹介する。

１．「検索と置換」はCtel+Fで開く
２．「検索と置換」が開いたら、「オプション」を押下
３．「書式」を押下で「書式の検索」ダイアログが開く
　　　※　「セルの書式設定」ダイアログとほぼ同様のもの。
４．開いたら、その中の項目を指定することにより検索ができる。
５．どれを選べばよいかわからない場合は、「セルから書式を
　　選択」を選ぶ。（図１）

名前定義　文字列・数値・数式に名前をつける　①

名前定義ダウンロード
▢　名前をつけられるもの
　　①　セルやセル範囲　2.　文字列や数値　3.　数式
▢　ブックレベルをシートレベル
　　①　ブックレベル
　　　　ブック内の全シートで使用可能　（名前ボックスで名前を付けるとブックレベルとなる）
　　②　シートレベル
　　　　特定のシートのみ（シートレベルをつける場合は「名前の管理」を使用）

１．文字や数値に名前を付ける
　　①　＜例＞名前=定数　数値＝2　を定義する（図1）
　　 　　　　　・文字列に名前を付ける場合も同様

名前定義　文字列・数値・数式に名前をつける　②

名前定義ダウンロード

2．計算式に名前を付ける
　①　A列のデータで、D2:E4のテーブルを検索しB列に
　　　その地域名を表示する。
　②　D2:E4は地域というテーブルを設定
　③　B3にVlookUpの計算式を入れる
　④　名前編集を開き名前を付ける(名前=計算)
　⑤　B3の計算式をコピーし参照範囲にペーストする。
　⑥　OKで閉じる。
　⑦　B3に「=計算」と入力すると「東京」が表示される。
　⑧　B3をB4:B8までコピーし完了

What-If分析（ゴールシーク）の使い方

What-If.分析ダウンロード

▢　問題　営業利益を　1,150 → 1,500 にするために数量をいくらにすればよいか？

▢　回答　数量が13,043の時に営業利益が1500 になる。

What-If分析（データテーブル）の使い方

What-If.分析ダウンロード

問題　「数量」と「広告宣伝費」を変化させた場合に「利益率」がどうなるか？