2014.10.15データの検索と抽出に力を発揮するVLOOKUP関数ですが、元データが必ずしも転記先と同じ形で作られていないことがあります。そんなときはMATCH関数と組み合わせてVLOOKUP関数の式を完成させて効率よくデータを抽出しましょう。「VLOOKUP関数」と「MATCH関数」を組み合わせて使うまとめおまけExcel関数の効率化にはデータの形を統一することが重要Excelを効率よく使うコツの1つに、データの形式を揃えておくことが挙げられます。仕事で使うエクセルのデータは、毎月同じものであることが多いです。受払データをとって、それを毎月更新していくような方法です。(このようなイメージ)このような表を作るときに気をつけたいのが、形式を統一すること。毎月作られる在庫データなどのファイルは、事前に集計するための式を組んでおいて、現場でカウントした集計結果のデータが届けば、それを貼り付けるだけで作業が完了するような状態にしています。赤枠で囲ったセルには、SUMIF関数が入力されています。SUMIF関数は条件を満たすデータを合計する関数ですが、ここでは「”仕入明細”シートのG列を検索して、左のセル(B5)の社名と一致したら、同じ行のF列の数値を合計しなさい」という指示になっています。いうことは、”仕入明細”シートにはG列に”仕入先”が、F列には”仕入合計”が、来るように元データを貼り付けなければ、正しい集計ができなくなるのです。このように、会社で毎月作られるようなファイルは、同じ形で元データを作ってくれることを前提に作業しているのが通常です。形式が変わってしまうと、元々組んでいた式を変更することになり余計な手間がかかりますし、何よりミスの原因になります。形式を統一しておけばそのようなリスクを避けられるので、みだりに変更しないようにしましょう。横に長いデータを検索するのに便利な「MATCH関数」これまで見てきたように、毎月作られるExcelファイルで一度式を入力してしまえば、記録された元データを貼り付けるだけで、作業が済んでしまうことになります。それでも、最初にファイルを作るときは、そうも行きません。正確さを期して、慎重に式を入力することになります。VLOOKUP関数は式の要素が多く、1度で正確な式を組むのはなかなか難しいです。3番目に入力する「どの列のデータを表示させるか」を意味する列番号を決めるのが面倒です。そんな時に便利なのが、MATCH関数です。MATCH関数は、指定したデータが、指定した範囲の中で何番目にあたるかを教えてくれる関数です。指定するデータを”バルセロナ”、指定範囲を青枠で囲んだセルにします。指定範囲の1番目のセル(左から数えます)は「B1セル」で、”バルセロナ”は「G1セル」なので列の順番で数えると6番目。すると、MATCH関数の結果は”6”を表示するという仕組みです。具体的にどのように入力しているかを見てみます。MATCH関数の要素は3つ。1は検索値(=指定するデータ)です。例に出した関数では「B1セル」になっていて、「B1セル」を見てみると”バルセロナ”が入っています。これで「”バルセロナ”に一致するデータを見つけなさい」と指示していることになります。”$B$2:$G$2”と入力されていて「B2セルからG2セルまでを範囲としなさい」と言う指示です。赤枠で囲まれているところです。”0”が入力されていますが、これは「完全に一致した場合だけデータを表示しなさい」という指示です。ここは、あまり気にせず”0”としておけばOKです。1〜3を指定すれば、2の範囲で「左から数えて何番目に1と一致するデータが存在するか」を答えてくれます。「VLOOKUP関数」と「MATCH関数」を組み合わせて使う「MATCH関数」の仕組みが分かると、「VLOOKUP関数」の入力が効率的になります。40)このような式で表されて、検索条件に合うデータを抽出する関数です。1は検索値(=検索条件)です。これに合うデータがあるかどうかを探します。今回は商品コードで検索します。この範囲を対象にして、検索値に合うデータがあるかどうかを探します。検索するのは指定範囲の中で最も左に来る列で、上の式の場合はB列を検索することになります。検索値と一致するデータが見つかったとき、2で指定した範囲の一番左の列から何番目の列のデータを表示するかを指定します。上の式の場合は、一番左の列がB列で列番号が”8”。B列から数えて8番目の列ですから、I列のデータを抽出することになります。”0”は検索値と完全に一致した場合にだけ結果を表示せよ、と言う意味です。以上を踏まえて、VLOOKUP関数の中にMATCH関数を無味合わせて使います。入力した内容をを見て下さい。ポイントは、MATCH関数の3−1検索値を”行だけを絶対参照(=コピー&ペーストしても、行だけは固定する方法)”で指定することです。ここでは、VLOOKUP関数全体の説明は省き、組み込んだMATCH関数のみ説明していきます。ポイントとなる3−1の検索値に注目します。入力内容は、「H$19」となっていて、列のHには”$”がなく19行目を示す”19”にだけ”$”がついています。このように検索値を指定すると、このセルをコピー&ペーストしたときに、行は19行目で固定されますが、列は移動範囲に合わせて変化します。これが先述した”行だけを絶対参照にする”意味です。最初に入力したセルの右斜め下に、この式をコピー&ペーストしてみます。行は”19”のままですが、列が”I”となり、最初のセルの”H”から一つ移動していることがおわかりいただけると思います。MATCH関数が示す結果も見てみましょう。VLOOKUP関数に組み込んだMATCH関数を抜き出して、それぞれのセルで表示させてみると「払出日」が8、「払出数量」が9と表示されて、正しく列番号を指定しているのが分かります。このようにして1つのセルで、表の先頭行にある項目に合わせて、検索値を指定するように、”行だけを絶対参照”にして入力できれば、あとはコピー&ペーストで正しく列番号を指定できるのです。元データをにらみながら「”払出日”は左から8番目にあるから列番号は8」のように、列番号を数えて指定する必要がなくなるので、効率よく入力できます。説明が前後しましたが、3−2は「検索範囲の指定」で、元データで項目を表示している、先頭行を指定します。検索範囲はどのセルでも共通していますので、”行も列も絶対参照”になるように指定します(「$B$4:$M$4」となっていて、列を表す”B”、”M”の左、行を表す”4”の左に$がついていることを確認して下さい)。3ー3は検索の型です。”0”として完全に一致する場合のみ表示するようにしています。完全に一致するものがなければエラーとなります。以上のようにして一つのセルにMATC関数を組み込んで、コピー&ペーストしてできあがったのが次の表です。項目ごとに列番号を指定しなくても、「MATCH関数」を使った「VLOOKUP関数」を入力して、コピー&ペーストするだけで、表全体を仕上げることができました。まとめExcelの関数は組み合わせて使うことで威力を増します。テクニック知ることで発想も豊かになりますから、新しいExcel関数の技術をどんどん吸収して行きましょう。おまけ最近はアウトプットに偏った時間の使い方をしているので、インプットを増やせるように時間管理を見直す予定です。両方並び立って初めて効果的な仕事ができますからバランスには気を配っています。コメント欄のフォントを色づけする方法ですExcelには、シート上の特定のセルにコメントを残す機能があります。コメント欄に入力した文字には、通常のシートの場合と同じように色づけなどで強調することもできます。折れ線グラフの縦軸を、小さい数字が上(昇順)になるように変更します複数期間の数字の移り変わりを、分かりやすく表示するのに向いているのが折れ線グラフ。Excelの折れ線グラフの縦軸は、数字が大きい方が上に来るようになっていますが、反対に、小さい方を上に来るようにすることも可能です。表の中にある”離れたデータ””一部のデータ”をグラフにする方法ですExcelは、グラフを簡単に作成できるのも1つの特徴です。データさえそろっていれば、かなり柔軟にグラフの作成ができます。Excelのグラフ作成は、つぎの2つの手順だけで完了します。考えて書店で本を買っています毎月本を買います。先月は約30冊、今月も昨日までで14冊購入していました。以前はネットから購入することが多かったのですが、今は書店で購入する方が多くなっています。変化の理由は効率性の違いです。使い分け「絶対参照」と「相対参照」は、Excelを使いこなす上で差が出やすいところです。実務の現場では、両方をしっかり使い分けられるようにしておく必要があります。複数条件にあてはまるデータの数を数える方法です経理や人事で仕事をしていると、住民税や社会保険料の支払額を求めるために、特定の条件に当てはまる人の数を数えることがあります。そのようなケースでも、Excelの関数を使うと効率よくカウントすることができます。東上野で税理士事務所タカジムを経営しています。公認会計士として監査法人に勤務し、上場企業の監査を担当。側面から会社の「利益」と「お金」を守るためのサポートしています。おっと、それから、監査、経理の現場でExcelを上手く使えず悔しい思いをした経験から(今でも当時の感情が蘇るくらい悔しかったみたいですよ)、”実務で使えるExcel”を伝えることにも力を入れています。東上野で税理士事務所タカジムを経営しています。公認会計士として監査法人に勤務し、上場企業の監査を担当。側面から会社の「利益」と「お金」を守るためのサポートしています。おっと、それから、監査、経理の現場でExcelを上手く使えず悔しい思いをした経験から(今でも当時の感情が蘇るくらい悔しかったみたいですよ)、”実務で使えるExcel”を伝えることにも力を入れています。lp=this.data.ListPrice,la=Number(lp.Amount),os=this.data.OfferSummary||{},sp=os.LowestNewPrice||{},sa=Number(sp.Amount);return(!isNaN(sa)&&saタイトルとURLをコピーしました。