メディア

資料を各段に見やすくするExcel関数の組み合わせ技3選

資料のグラフや表をワンランクアップさせるExcel関数の組み合わせ技を紹介する。

» 2024年02月26日 10時06分 公開
[キーマンズネット]

 「Microsoft Excel」(以下、Excel)で資料作成や集計などをする人は多いだろう。時間をかけて作成した資料も、伝わりづらいもの、使いにくいものになってしまってはもったいない。本稿では、資料のグラフや表をワンランクアップさせるための便利なExcel関数を紹介する。

資料を各段に見やすくするExcel技3選

検索ボックスを配置する

 名簿や商品の一覧表などを作成する際、FIND関数とAND関数を組み合わせて条件付き書式を適用させると、表内に検索ボックスを設置できる。「ActiveX コントロール」を使用することで見やすさがさらにアップする。

 まずは、検索範囲を選択し、ホームタブの[条件付き書式]から[新しいルールを追加]を選ぶ(図1)。

図1(出典:日本マイクロソフトの「X」公式アカウント)

 次に、[数式を使用して、書式設定するセルを指定]をクリックし、FIND関数で検索範囲を指定した上で、AND関数をネストして複数条件を適応させ、[書式]をクリックする(図2)。入力する数式は以下の通りだ。

=AND(FIND($B$1,A3),$B$1<>"")

図2(出典:日本マイクロソフトの「X」公式アカウント)

 その後、既に開いている「セルの書式設定」のウィンドウで、フォントや塗りつぶしなど任意の書式を設定する(図3)。この時点で、図4のように、セル「B1」に「品川」と入力すると、設定した書式に合わせてセル「C3」の「品川」がヒットする。

図3(出典:日本マイクロソフトの「X」公式アカウント)
図4(出典:日本マイクロソフトの「X」公式アカウント)

 このままでも分かりやすいが、セル「B1」と「C1」を結合させることで見た目を整えられる。まずは開発タブの[挿入]ー[アクティブXコントロール]ー[テキストボックス]を選択した状態で、ドラッグ&ドロップでセル「B1」と「C1」を結合させる(図5、6)。さらに[プロパティ]を選択し、「LinkedCell」と書かれたボックスの横に「B1」と入力して完了だ(図6)。

図5(出典:日本マイクロソフトの「X」公式アカウント)
図6(出典:日本マイクロソフトの「X」公式アカウント)

営業利益などの増減を可視化する

 決算書や報告書などの利益表でウオーターフォールチャートの作成時に、数字の値が大きく増減を比較しにくいことはないだろうか。この場合は、ROUND関数で桁数を指定、四捨五入すると比較対象の差異が見やすくなる。

 まずは、ウオーターフォールチャートにしたいグラフを選択し、挿入タブで[ウオーターフォールチャート]をクリックする(図7)。

図7(出典:日本マイクロソフトの「X」公式アカウント)

 このままでは値が大きく、変化が分かりにくいので万単位で表示することにする。図8の「万単位→」と印が付けられた列には「ROUND(B8/10000.0)」という関数が挿入され、万単位の数字が入力されている(図9)。これを次の作業でグラフに反映する。

図8(出典:日本マイクロソフトの「X」公式アカウント)
図9(出典:日本マイクロソフトの「X」公式アカウント)

 1万単位に直した数字をグラフに反映させるためには、ウオーターフォールチャートのグラフ内をクリックし、[データの選択][編集]を選択する(図10、11)。

図10(出典:日本マイクロソフトの「X」公式アカウント)
図11(出典:日本マイクロソフトの「X」公式アカウント)

 「系列値」に入力されているセルを削除し、反映したいセルをドラッグ&ドロップで選択する。この場合、系列値は円単位のセルの「Sheet1!$G$4:$L$4」から万単位のセルの「Sheet1!$G$5:$L$5」に変更されている。最後に[OK]をクリックして完了だ(図12、13)。

図12(出典:日本マイクロソフトの「X」公式アカウント)
図13(出典:日本マイクロソフトの「X」公式アカウント)

日付の文字列データを形式変換

 最後は日付の文字列データの形式を変換するTipsだ。CSVなどから文字列として出力された日付データを、DATEVALUE関数とTEXT関数を組み合わせて任意の日付形式に変更できる。DATEVALUE関数は文字列から日付のシリアル値を返し、TEXT関数は表示形式を指定する。

 セル「C9 」に入力された日付「2024-01-15」は、以下の数式を挿入することで「2024年1月15日」と表記を変更できる。

=TEXT(DATAVALUE(C9),"yyyy年mm月dd日")

図14(出典:日本マイクロソフトの「X」公式アカウント)

Copyright © ITmedia, Inc. All Rights Reserved.

会員登録(無料)

製品カタログや技術資料、導入事例など、IT導入の課題解決に役立つ資料を簡単に入手できます。