変わりたい組織と、成長したいビジネスパーソンをガイドする

会員登録をすると、編集者が厳選した記事やセミナー案内などをメルマガでお届けしますNIKKEIリスキリング会員登録最新情報をチェック

縦と横に項目が並んだ一覧表を1件1行形式に作り替えたいことがある。医療費の集計表などはその典型だ(図1上)。受診者ごとに支払先と金額をコピペしたい場合、受診者の順で縦一列にデータが並んでいたほうが扱いやすい(図1下)。

図1 縦と横に項目が並んだ表を1件1行の形式に作り直してみよう。ピボットテーブルなら集計表のレイアウトを変えれば一発だが、そうでない一般的な表の変換方法を、Microsoft 365などの最新版Excelで考えてみる。最新版でない人は、ウェブ版Excelで試してみてほしい

図1 縦と横に項目が並んだ表を1件1行の形式に作り直してみよう。ピボットテーブルなら集計表のレイアウトを変えれば一発だが、そうでない一般的な表の変換方法を、Microsoft 365などの最新版Excelで考えてみる。最新版でない人は、ウェブ版Excelで試してみてほしい

ピボットテーブルなら集計表のレイアウトを変えれば即解決だが、一般的な表ではどうすればよいか? それが今回のテーマだ。Microsoft 365などの最新版Excelの機能を駆使して挑んでみよう。

ステップバイステップで解決へと導こう。まずは数式の意味を理解しやすいように、元表の3つのセル範囲に名前を付けておく(図2)。

図2 金額のセル範囲に「金額」と名前を付ける(1、2)。縦横の項目名の範囲にも、それぞれ「受診者」「支払先」と名前を付けておく。これで、以降で組み立てる数式が驚くほどわかりやすくなる

図2 金額のセル範囲に「金額」と名前を付ける(1、2)。縦横の項目名の範囲にも、それぞれ「受診者」「支払先」と名前を付けておく。これで、以降で組み立てる数式が驚くほどわかりやすくなる

1×3と4×1と4×3、配列同士の演算結果に注目

1件1行形式にするには、受診者、支払先、金額をセットにしたデータを作る必要がある。一番簡単なのは文字列として連結してしまう方法だ。項目名などで未使用の文字を区切り文字として、3つの項目を連結する。最新版Excelなら図3の数式で、セル範囲「金額」と同じ大きさの動的配列が得られる。動的配列とは仮想的なセル範囲のようなもので、数式を入力したセルの右や下が空いている場合は内容が展開表示される(スピル機能)。

図3 B6セルに図の数式を入力する。結果は金額と同じ4列3行の配列となり、それぞれの金額と同じ行の受診者および同じ列の支払先が、区切り文字(ここでは「;」)と一緒に「&」演算子で連結される。行見出しと列見出しをデータ本体と連結する配列数式は「=行見出し&区切り文字&列見出し&区切り文字&データ本体」と、パターンで覚えておこう。区切り文字は項目名などで使っていない文字にする

図3 B6セルに図の数式を入力する。結果は金額と同じ4列3行の配列となり、それぞれの金額と同じ行の受診者および同じ列の支払先が、区切り文字(ここでは「;」)と一緒に「&」演算子で連結される。行見出しと列見出しをデータ本体と連結する配列数式は「=行見出し&区切り文字&列見出し&区切り文字&データ本体」と、パターンで覚えておこう。区切り文字は項目名などで使っていない文字にする

この配列数式では1列×3行の受診者と4列×1行の支払先、4列×3行の金額を演算(文字列連結)している。結果は4列×3行の配列となり、その各要素では金額と同じ行の受診者および同じ列の支払先が参照される。行見出しと列見出しをデータ本体と連結する定型パターンとして覚えておこう。

新着記事

Follow Us
日経転職版日経ビジネススクールOFFICE PASSexcedo日経TEST

会員登録をすると、編集者が厳選した記事やセミナー案内などをメルマガでお届けしますNIKKEIリスキリング会員登録最新情報をチェック