資料のグラフや表をワンランクアップさせるExcel関数の組み合わせ技を紹介する。
「Microsoft Excel」(以下、Excel)で資料作成や集計などをする人は多いだろう。時間をかけて作成した資料も、伝わりづらいもの、使いにくいものになってしまってはもったいない。本稿では、資料のグラフや表をワンランクアップさせるための便利なExcel関数を紹介する。
名簿や商品の一覧表などを作成する際、FIND関数とAND関数を組み合わせて条件付き書式を適用させると、表内に検索ボックスを設置できる。「ActiveX コントロール」を使用することで見やすさがさらにアップする。
まずは、検索範囲を選択し、ホームタブの[条件付き書式]から[新しいルールを追加]を選ぶ(図1)。
次に、[数式を使用して、書式設定するセルを指定]をクリックし、FIND関数で検索範囲を指定した上で、AND関数をネストして複数条件を適応させ、[書式]をクリックする(図2)。入力する数式は以下の通りだ。
=AND(FIND($B$1,A3),$B$1<>"")
その後、既に開いている「セルの書式設定」のウィンドウで、フォントや塗りつぶしなど任意の書式を設定する(図3)。この時点で、図4のように、セル「B1」に「品川」と入力すると、設定した書式に合わせてセル「C3」の「品川」がヒットする。
このままでも分かりやすいが、セル「B1」と「C1」を結合させることで見た目を整えられる。まずは開発タブの[挿入]ー[アクティブXコントロール]ー[テキストボックス]を選択した状態で、ドラッグ&ドロップでセル「B1」と「C1」を結合させる(図5、6)。さらに[プロパティ]を選択し、「LinkedCell」と書かれたボックスの横に「B1」と入力して完了だ(図6)。
決算書や報告書などの利益表でウオーターフォールチャートの作成時に、数字の値が大きく増減を比較しにくいことはないだろうか。この場合は、ROUND関数で桁数を指定、四捨五入すると比較対象の差異が見やすくなる。
まずは、ウオーターフォールチャートにしたいグラフを選択し、挿入タブで[ウオーターフォールチャート]をクリックする(図7)。
このままでは値が大きく、変化が分かりにくいので万単位で表示することにする。図8の「万単位→」と印が付けられた列には「ROUND(B8/10000.0)」という関数が挿入され、万単位の数字が入力されている(図9)。これを次の作業でグラフに反映する。
1万単位に直した数字をグラフに反映させるためには、ウオーターフォールチャートのグラフ内をクリックし、[データの選択][編集]を選択する(図10、11)。
「系列値」に入力されているセルを削除し、反映したいセルをドラッグ&ドロップで選択する。この場合、系列値は円単位のセルの「Sheet1!$G$4:$L$4」から万単位のセルの「Sheet1!$G$5:$L$5」に変更されている。最後に[OK]をクリックして完了だ(図12、13)。
最後は日付の文字列データの形式を変換するTipsだ。CSVなどから文字列として出力された日付データを、DATEVALUE関数とTEXT関数を組み合わせて任意の日付形式に変更できる。DATEVALUE関数は文字列から日付のシリアル値を返し、TEXT関数は表示形式を指定する。
セル「C9 」に入力された日付「2024-01-15」は、以下の数式を挿入することで「2024年1月15日」と表記を変更できる。
=TEXT(DATAVALUE(C9),"yyyy年mm月dd日")
Copyright © ITmedia, Inc. All Rights Reserved.
製品カタログや技術資料、導入事例など、IT導入の課題解決に役立つ資料を簡単に入手できます。