メンバー

以下のような各商品の購入数を管理している表と、各商品の最終購入日を抽出している表があります。
この最終購入日を抽出する方法について解説していきます。

最終購入日を抽出する方法

抽出する方法について２通りで解説していきます。
１つ目は「XLOOKUP関数で抽出する方法」、２つ目は「VLOOKUP関数で抽出する方法」になります。

一番簡単な方法は１つ目の方法ですが、XLOOKUP関数は比較的新しい環境（2021以降）でないと扱うことができません。
そのため、対応していない場合は、２つ目の方法で実現する必要があります。

１.XLOOKUP関数で抽出する方法

VLOOKUP関数やMATCH関数は、検索値が上から先に見つかった位置の情報を返します。
※VLOOKUP関数の使い方は、後半で解説しています。

そのため、下に行くほど最新データになる表で、最新の情報のみを抽出することが難しいです。
上記の例の場合、最新情報が必要だとすると、理想的な値は以下のようになります。

XLOOKUP関数では、このような値を簡単に求めることができます。
XLOOKUP関数の使い方は以下になります。

=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
// 検索値：検索したい値
// 検索範囲：検索したいセルの範囲や配列
// 戻り範囲：抽出したいセルの範囲や配列
// [見つからない場合]：検索値が検索範囲の中から見つからない場合に表示する値
// → 省略時は見つからない場合にエラーを表示
// [一致モード]：以下の中から検索方法を選択
// 0：完全一致（省略時）、-1：完全一致または次に小さい項目、1：完全一致または次に大きい項目、2：ワイルドカード文字との一致
// [検索モード]：以下の中から検索方法を選択
// 1：先頭から末尾へ検索（省略時）、-1：末尾から先頭へ検索、2：バイナリ検索（昇順の検索範囲）、-2：バイナリ検索（降順の検索範囲）

XLOOKUPの引数には[検索モード]があり、ここで「末尾から先頭への検索」を指定することで、最新情報を抽出することができます。
また、VLOOKUP関数とは異なり、「検索したい項目が表の一番左にある必要がある」などの縛りがありません。

XLOOKUP関数を用いて求めた数式は以下になります。

=XLOOKUP(F3,C:C,B:B,"",0,-1)
// データの増減に対応するため、「C:C」などと列全体を指定しています

２.VLOOKUP関数で抽出する方法

次にXLOOKUP関数が使えない環境でも実現できるように、VLOOKUP関数で実現する方法について解説していきます。
VLOOKUP関数の使い方は以下になります。

=VLOOKUP(検索値, 範囲, 列番号, [検索方法])
// 検索値：検索したい値
// 範囲：検索したい項目が一番左の列である抽出対象の列を含む範囲を指定
// 列番号：範囲から抽出したい列が左から何番目なのかを指定
// [検索方法]：以下の中から検索方法を選択
// TRUE：近似一致（省略時）、FALSE：完全一致

VLOOKUP関数は、検索したい項目が抽出対象の列より左にある必要があるため、以下の表では抽出することができません。

仮に、[日付]と[商品名]の項目が逆だったとしても、VLOOKUP関数は「検索値が上から先に見つかった位置の情報」を返すので正しく抽出することができません。

=VLOOKUP(F3,B:C,2,FALSE)

そのため、VLOOKUP関数で表の中の最終購入日を抽出するには、検索用の項目を用意する必要があります。
またその項目は、重複のない項目である必要があります。

ということで、処理用の列を以下のように挿入し追加します。

では、まずはB列の検索用の値を求めていきます。
重複しないように処理列の項目に関しては、「商品名+”-“+登場回数」にしていきます。
セルB3の場合は「A-1」、セルB8の場合は「A-2」というイメージになります。

登場回数は、３行目から対象の行までに出てきた指定の商品名の数になります。
そのような値は、COUNTIF関数で求めることができます。

=COUNTIF(範囲, 検索条件)
// 指定した範囲内に検索条件を満たすセルの数を返す

セルB3を求める場合の対象の範囲は「D3からD3」になります。
セルB4を求める場合の対象の範囲は「D3からD4」になります。
という感じに、開始のセルD3は固定で、終わりのD3やD4は対象の行までになります。

そのため、相対参照と絶対参照を上手く活用し、コピーするだけで求めることができる数式を求めます。
その数式は以下のようになります。

=COUNTIF($D$3:D3,D3)

後は、こちらの数式に商品名を加えます。

=D3&"-"&COUNTIF($D$3:D3,D3)

後は、以下の抽出対象の処理列を求めます。

抽出対象は、「対象の商品名」と「表の中で各商品名が存在する数」を組み合わせることで最新の検索用の値を求めることができるかと思います。
各商品が存在する数は以下のように求めることができます。

=COUNTIF(D:D,H3)

後は、こちらの数式に商品名を加えます。

=H3&"-"&COUNTIF(D:D,H3)

このようにしてVLOOKUP用の検索値を求めることができました。
最後に、VLOOKUP関数で最終購入日を抽出します。

=VLOOKUP(G3,B:C,2,FALSE)

このようにして求めることができました。

まとめ

前半のXLOOKUP関数は便利な関数ですが、2021以降のExcelでないと対応しておりません。
そのため、他の方に共有する資料などでは、対応していない可能性を考慮して、後半のVLOOKUP関数を用いて求めるのが好ましいかと思います。

以下のような売上表があります。

この表の中のトップ３の売り上げをセルD3からD5に求める場合は、LARGE関数が活用できます。
ただ、LARGE関数で抽出した数値は、元の表をフィルターで絞り込んでも更新されません。

ということで今回は、「LARGE関数でトップ３を抽出する方法」と、フィルターで絞り込んだ場合に、「非表示のセルを除外してトップ３を抽出する方法」について解説していきます。

LARGE関数でトップ３を抽出する方法

まず、LARGE関数の使い方から確認します。

=LARGE(配列, 順位)
// 指定した配列の中から指定した順位番目の大きな値を返す

ということで活用した例は以下になります。

=LARGE($D$8:$D$17,C3)
// セルD3に上記の数式を入力し、D5までコピーするだけで反映できるように、D8:D17を絶対参照（$D$8:$D$17）にする
// 絶対参照にしたセルのアドレスは、コピー時に相対的に移動しないようになる

LARGE関数の引数に渡した値は、フィルターで非表示にしたとしても除外されません。
そのため、以下のようにフィルターで絞り込んでも表示される結果は同じになります。

この次に、フィルターなどで非表示になったデータを除外してトップ３を求める方法について解説していきます。

補足

LARGE関数とセットにSMALL関数も覚えておくと役立つかと思います。

=SMALL(配列, 順位)
// 指定した配列の中から指定した順位番目の小さな値を返す

活用例は以下になります。

=SMALL($D$8:$D$17,C3)

SMALL関数に関しても、非表示のセルは除外されません。

こちらに関しても次の方法で、非表示を除外することができます。

非表示のセルを除外してトップ３を抽出する方法

非表示のセルを除外してトップ３を抽出することができる関数は、AGGREGATE関数になります。
この関数は、LARGE関数を含む１９種類の関数を集約した関数になります。
また、単純に１９種類の関数が使えるだけではなく、非表示の無視やエラーの無視なども引数で設定ができる１９種類の関数を強化した関数になります。

=AGGREGATE(集計方法, オプション, 配列, [順位])
=AGGREGATE(集計方法, オプション, 参照1, 参照2, [参照3], …)

// 集計方法：以下の1~19の値で指定
・1 - AVERAGE(平均値)
・2 - COUNT(数値の個数)
・3 - COUNTA(データの個数)
・4 - MAX(最大値)
・5 - MIN(最小値)
・6 - PRODUCT(積)
・7 - STDEV.S(不偏標準偏差)
・8 - STDEV.P(標本標準偏差)
・9 - SUM(合計値)
・10 - VAR.S(不偏分散)
・11 - VAR.P(標本分散)
・12 - MEDIAN(中央値)
・13 - MODE.SNGL(最頻値)
・14 - LARGE(降順の順位) ← 今回はこちらを活用
・15 - SMALL(昇順の順位)
・16 - PERCENTILE.INC(百分位数)
・17 - QUARTILE.INC(四分位数)
・18 - PERCENTILE.EXC(百分位数[0%と100%を除く])
・19 - QUARTILE.EXC(四分位数[0%と100%を除く])

// オプション：以下の0~7の値で指定
・0または省略 - ネストされたSUBTOTAL関数とAGGREGATE関数を無視
・1 - 0の指定に加えて非表示の行を無視
・2 - 0の指定に加えてエラー値を無視
・3 - 0の指定に加えて非表示の行とエラー値を無視
・4 - 何も無視しない
・5 - 非表示の行を無視 ← 今回はこちらを活用
・6 - エラー値を無視
・7 - 非表示の行とエラー値を無視

// 残りの引数は、集計方法で指定した関数と同じ

今回は、こちらの集計方法「14：LARGE」とオプション「5：非表示の行を無視」を使用します。 集計方法とオプションを指定したら、残りの引数はLARGE関数と同じになります。

=LARGE(配列, 順位)
=AGGREGATE(14 , 5, 配列, 順位)

そのため、以下のように活用することができます。

=AGGREGATE(14,5,$D$8:$D$17,C3)

こちらの場合、オプション「5：非表示の行を無視」を指定しています。
そのため、以下のようにフィルターで絞り込むことで、表示されているセルの中で集計することができます。

補足

SMALL関数に関しても同様に使用することができます。

集計用の関数の強化版として、AGGREGATE関数の存在を覚えておくと、いざという時に活用できるかと思います。
ちなみに、AGGREGATE関数に似た関数として、SUBTOTAL関数があります。

AGGREGATE関数は、SUBTOTAL関数を更に進化させた関数になります。
進化した点は、主に以下になります。

・対応関数を８種類追加
・オプションの引数を追加(エラーを無視できるなど)

＞SUBTOTAL関数について

会社名などに関して、半角文字や全角文字が混ざっている表をたまに見かけます。
半角文字や全角文字は統一したほうが、集計や検索がしやすくなるかと思います。

ただ、パソコンの操作に慣れていない方の場合、半角文字と全角文字の違いが分からず、以下のような同じ意味の違う文字列を入力していることがあると思います。

・「田中　太郎」と「田中 太郎」
・「株式会社ﾘﾝｺﾞ」と「株式会社リンゴ」

このような入力ミスを防ぐために、半角文字を予め入力できないようにしたり、半角文字を入力した際に、セルの背景色を変えるなどとするのが良いかと思います。
今回は、その設定方法について解説していきます。

特定の文字を入力できないようにする場合は「データの入力規則」、特定の文字が入力されている時にセルの書式を変更する場合は「条件付き書式」を活用します。
どちらの設定でも、設定する数式は、ほぼ同じになります。

【データの入力規則】半角文字を予め入力できないようにする

まずは「テータの入力規則」で入力を制限する場合の数式を作成していきます。
設定する条件式は、「半角文字が含まれない場合」としていきます。

これを数式で求める場合は、LEN関数とLENB関数を活用します。

=LEN(文字列)
// 半角全角の区別なく、1文字を1として数えて合計を返す

=LENB(文字列)
// 半角文字は1、全角文字は2として数えて合計を返す

LEN関数とLENB関数の違いは、全角文字の数え方になります。
つまり、LEN関数で求めた値を２倍した値とLENB関数で求めた値に違いがあれば、半角文字が混ざっていることになります。

その内容を数式で表現すると、以下のようになります。

=LENB(B3)=LEN(B3)*2

ということで、以下の数式を満たしている時のみ入力を許可する設定を行います。

=LENB(B2)=LEN(B2)*2

設定したいセル全体を選択し、[データ]タブの中の[データの入力規則]を選択します。

以下の画面にて、[入力値の種類]を[ユーザー設定]にし、[数式]に先ほどの数式を設定します。

設定した数式の基準（セルB2）は、アクティブになっているセルである必要があります。
こちらの場合は、複数選択の中でもセルB2がアクティブになっているため、セルB2を基準とした数式にしています。
他の選択しているセルには、こちらで設定した数式がアクティブのセルから相対参照で設定されます。

上記のような設定をすることで、以下のように半角文字の入力を制限することができました。

【条件付き書式】半角文字を入力した際に、セルの背景色を変える方法

次に「条件付き書式」で「半角文字が入力されている場合」という数式を作成していきます。
先ほどの「データの入力規則」では、「半角文字が含まれない場合」だったため、逆の数式になります。

先ほど設定した数式は以下になります。

=LENB(B2)=LEN(B2)*2

この数式を「等しくない場合」にする必要があります。
「等しい」は「=」で表現し、「等しくない」は「<>」で表現します。
そのため、下記のような数式になります。

=LENB(B2)<>LEN(B2)*2

では、この数式を条件付き書式で設定していきます。

設定したい範囲を選択し、[ホーム]タブの中の[条件付き書式]から[新しいルール]を選択します。

以下の画面にて、[ルールの種類を…]を[数式を使用して、…]にし、[次の数式を…]に先ほどの数式を設定します。
条件を満たしている時に設定する書式に関して、こちらでは「背景色：黄色」を設定しています。
設定した数式の基準（セルB2）は、先ほどと同じく、アクティブになっているセルにする必要があります。

上記のような設定をすることで、以下のように半角文字をが含まれるセルの背景色を黄色にすることができました。

以下の成績一覧表を元に、点数が６０点以上の方のみに合格証のシートを発行する機能の開発方法について解説していきます。

開発準備

まずは、以下のような成績一覧表を用意します。
こちらでは、B列にID、C列に名前、D列に点数の項目を用意しています。
また、成績情報は３行目以降に入力しています。

次に、発行する合格証の以下のようなテンプレートを用意します。
こちらでは、セルC4に合格者の名前を入力する場所を用意しています。

こちらでは、VBAを用いて開発していきます。
VBAに関して、一度も開発したことがない場合は、下記よりVBAの基礎をご確認ください。

＞VBAの基礎（4-04くらいまでの理解が必要です）

開発

では、開発していきます。
[開発]タブの中の[マクロ]から”合格証発行”という名前で以下のように開発します。

Option Explicit '省略可 変数の宣言を強制する

Sub 合格証発行()
    
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("成績一覧")
    Set ws2 = Worksheets("合格証")
    
    Dim lastRow As Long
    lastRow = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row
    
    Dim i As Long
    For i = 3 To lastRow
        '発行条件
        If ws1.Cells(i, "D").Value >= 60 Then
            ws2.Copy After:=Worksheets(Worksheets.Count) '合格証発行
            With Worksheets(Worksheets.Count)
                .Name = ws1.Cells(i, "C").Value 'シート名
                .Range("C4").Value = ws1.Cells(i, "C").Value & " 様" '合格者名
            End With
        End If
    Next i
    
End Sub

１）[ws1,ws2]：シート情報を格納する用の変数を用意し"成績一覧"シートと"合格証"シートを設定する

２）[lastRow]："成績一覧"シートの表のデータが入力されている最後の行番号を取得する
→「ws1.Cells(ws1.Rows.Count, "B")」で"成績一覧"シートのB列の最終行を選択し、「.End(xlUp)」で「Ctrl+↑」して移動して止まった位置の行番号「.Row」を取得する

３）[For~Next]："成績一覧"シートのD列(点数)の値が60点以上のデータを3行目から「２）」で取得した行数まで確認する
→60点以上の場合は、「ws2.Copy After:=Worksheets(Worksheets.Count)」で"合格証"シートをコピーし、最後尾にシートを追加する
→コピーした最後尾のシート名を「.Name = ws1.Cells(i, "C").Value」で合格者の名前に変更し、合格証の合格者の名前を「Range("C4").Value = ws1.Cells(i, "C").Value」で"○○様（○○は合格者名）"という値に変更する

こちらの内容を実行すると、以下のように合格者にだけ合格証のシートが作成されます。

補足

発行されたシートのみを全て印刷したい場合は、合格証の最初のシート「増井星輝」を選択し、Shiftキーを押しながら最後のシート「野上将文」を選択します。
そのようにすると、以下のように一括で複数シートを選択することができます。

この状態で「Ctrl+P」などで印刷画面に移り、[設定]にて「作業中のシートを印刷」を選択することで、一括で印刷することができます。

書類のフォーマットで、１つ１つの枠に右詰めで文字を入力する必要があることがあります。
今回は、指定のセルに入力した文字を１つ１つの枠に右詰めで表示させる数式の作成方法について解説していきます。

実現方法

実現方法は、以下の流れを数式で表現します。

手順１：枠の数と文字の数を揃える
手順２：先頭から１文字ずつ枠に表示させる

手順１：枠の数と文字の数を揃える

まずは、手順１から確認していきます。
以下の画像の場合、枠の数は５つになります。
そのため、セルI2に入力した値が、５文字未満の場合は、文字の先頭にスペースを加えて５文字にしていきます。

先頭にスペースを加えて５文字にするためには、何文字のスペースが必要なのかを確認する必要があります。
そのため、セルI2に入力されている現時点の文字数をLEN関数を活用して取得します。

=LEN(文字列)
// 指定した文字列の文字数を返す（半角、全角に関わらず1文字としてカウント）

LEN関数を活用した例が以下になります。

=LEN(I2)

次に、上記で求めた値を５から引くことで、スペースが必要な数を求めることができます。

=5-LEN(I2)

上記で求めた数分だけスペースを先頭に加えていきます。
指定の数の文字を生成するには、REPT関数が活用できます。

=REPT(文字列, 繰り返し回数)
// 指定の文字列を指定の回数だけ繰り返して表示

実際に、LEN関数とREPT関数を組み合わせて、半角スペースを先頭に加えた例は以下になります。
※以下の画像では、半角スペースが分かりやすいように「_」で表現しています。

=REPT(" ",5-LEN(I2))&I2

手順２：先頭から１文字ずつ枠に表示させる

後は、「手順２：先頭から１文字ずつ枠に表示させる」を行います。
手順２は、上記で取得できた文字列から１文字ずつを枠に表示させるだけです。

特定の文字列の中から、一部の文字列を抽出するにはMID関数が活用できます。

=MID(文字列, 開始位置, 文字数)
// 指定された文字列の、指定された開始位置から指定された文字数分を返す

実際に、MID関数の引数[文字列]に上記のREPT関数の内容を入力し、開始位置を１～５で、それぞれ１文字取得して表示させてみます。

=MID(REPT(" ",5-LEN(I2))&I2,1,1)
=MID(REPT(" ",5-LEN(I2))&I2,2,1)
=MID(REPT(" ",5-LEN(I2))&I2,3,1)
=MID(REPT(" ",5-LEN(I2))&I2,4,1)
=MID(REPT(" ",5-LEN(I2))&I2,5,1)

このよう右詰めで１文字ずつ枠に当てはめることができました。

補足

上記の手順で作成した以下の数式を確認すると、MID関数の引数[開始位置]以外は全て同じ値であることが分かります。
また、引数[開始位置]は左から１～５の連番になっています。

=MID(REPT(" ",5-LEN(I2))&I2,1,1)
=MID(REPT(" ",5-LEN(I2))&I2,2,1)
=MID(REPT(" ",5-LEN(I2))&I2,3,1)
=MID(REPT(" ",5-LEN(I2))&I2,4,1)
=MID(REPT(" ",5-LEN(I2))&I2,5,1)

そのような場合は、COLUMN関数を活用することでオートフィルを用いて簡単に入力することができるようになります。

=COLUMN([参照])
// 参照の列番号を返す
// 省略時は数式が入力されているセルの列番号を返す

１～５の連番をCOLUMN関数を活用して求める場合、以下のようになります。

=COLUMN(A1)
=COLUMN(B1)
=COLUMN(C1)
=COLUMN(D1)
=COLUMN(E1)

この場合、先頭に「=COLUMN(A1)」だけ入力することで、後はコピーして相対参照で求めることができます。
こちらを活用して、以下の数式を１つ作成できれば、後は右にコピーするだけで抽出することができます。

=MID(REPT(" ",5-LEN($I$2))&$I$2,COLUMN(A1),1)
// COLUMN関数の引数以外は$を入力し絶対参照にする

以下の表をランダムに並べ替える方法について、スピルを使わない方法とスピルを使う方法の２通りで解説していきます。

＞スピルとは

スピルを使わない方法

スピルを使わずに並べ替えるには、並べ替え機能を活用します。
ただ、元の表のままでは、ランダムに並べ替えることができないため、ランダムに並べ替える用の乱数の項目を用意します。

乱数を発生させるには、RAND関数が便利です。

=RAND()
//「0≦x<1」の範囲のxをランダムに返す

以下のように項目を追加します。

=RAND()

先頭のセルにRAND関数を入力して、表の一番下までオートフィルを活用してコピーすると良いです。

この乱数は、再計算すると自動で更新されます。
[数式]タブの[計算方法の設定]が「自動」になっていない場合は、[再計算実行]やF9キーなどで再計算すると乱数が再設定されます。

乱数の項目を追加することができましたら、乱数の項目を基準に昇順（降順）に並べ替えることで順番をランダムにすることができます。
こちらではフィルターを使って解説していきます。

表の中を選択し、「Ctrl+Shift+L」などでフィルターを設定し、以下のように昇順（降順）にします。

そのようにすることで、以下のようにランダムに並べ替えることができました。
乱数の項目を昇順（降順）にすることで、何度でも並べ替えることができます。

スピルを使う方法

スピルを活用すると、先ほどのように昇順（降順）に並べ替えしたりすることなく、数式のみで、瞬時に表を並べ替えて表示させることができます。

ただ、こちらの方法では、数式を再計算すると順番がランダムに並べ替えられてしまいます。
そのため、順番の記録を残す必要がある場合は、値のみの貼り付けなどで再計算で変わらないようにする必要があります。

今回、活用する関数は、RANDARRAY関数とSORTBY関数になります。

=RANDARRAY([行],[列],[最小],[最大],[整数])
// 数式を入力したセルを基準に[行]×[列]の範囲に乱数を表示する
// [最小]と[最大]で乱数の発生範囲を指定する
// 省略時、[最小]は「0」、[最大]は「1」が設定される
// [整数]は、TRUEの場合、整数の乱数、FALSEの場合、実数（小数）の乱数を発生させる
// 省略時、FALSEが指定される

RANDARRAY関数の使用例は以下になります。

=RANDARRAY(10)
=RANDARRAY(4,3,,2,TRUE)
=RANDARRAY(,3,1,2,FALSE)

=SORTBY(配列, 基準配列1, [並べ替え順序1], [基準配列2, 並べ替え順序2], …)
// 指定した範囲（配列）に関して、基準配列を基準に並べ替えた範囲を返す
// [並べ替え順序]は、「1」の場合、昇順、「-1」の場合、降順になる
// 省略時は、[1」が指定される
// [基準配列、並べ替え順序]の2以降は、複数の項目を基準に並べ替えたい場合に活用する

SORTBY関数の使用例は以下になります。

=SORTBY(B3:C12,B3:B12)
=SORTBY(C3:C12,B3:B12,-1)
=SORTBY(B2:C12,B2:C2)

上記の真ん中の例を見るとイメージしやすいかと思いますが、基準配列は、表示させたい範囲外であっても問題ございません。
注意点として、行数（列数）の数に関しては同じにする必要があります。

この仕組みから、基準配列に関して、RANDARRAYを使って求めていきます。

=SORTBY(B3:C12,RANDARRAY(10))

このようにランダムに並べ替えることができました。

ただ、こちらの数式の場合、行数を直接「10」と入力しています。
指定の範囲の行数を取得する場合は、ROWS関数が便利です。

=ROWS(配列)
// 配列に含まれる行数を返す

ROWS関数の使用例は以下になります。

=ROWS(B3:B12)
=ROWS(B3:C12)

RANDARRAY関数と組み合わせると以下のようになります。

=RANDARRAY(ROWS(B3:B12))

後は、上記の乱数の配列を基準にSORTBY関数を活用して並べ替えます。

=SORTBY(B3:C12,RANDARRAY(ROWS(B3:B12)))

このように、表の順番をランダムに並べ替えることができました。

改行を含むセルを参照する数式を入力したにも関わらず、改行がなくなって表示されることがあります。
こちらでは、改行を保持して参照する方法について解説していきます。

原因と対策

参照元の改行が反映されない原因は、セルの書式設定にあります。
「改行」は「改行」という特殊な値が入力されているため、表示されている値に改行がなくても「改行」という特殊な値は入力されています。
「改行」という特殊な値を「改行」として表示させるには、セルの書式の設定を変更する必要があります。

「改行」に対応している設定は、[折り返して全体を表示する]という設定です。
設定方法は、対象のセルを選択し、[ホーム]タブの中の、[折り返して全体を表示する]を選択します。

この設定を行うことで、以下のように参照元と同じ位置で改行して表示させることができます。

別々のセルを改行して結合する

上記の理屈から以下のように、別々のセルを改行して結合することも実現できます。

この方法は、「改行」という特殊な値を加えて結合するという方法になります。
「改行」という特殊な値は、CHAR関数を活用することで入力することができます。

=CHAR(数値)
// 指定した数値に対応している文字を返す

改行の文字コードは”10″になります。
そのため、以下のように、セルの値を結合する際に「改行」を加えます。

=B2&CHAR(10)&B3&CHAR(10)&B4

「改行」という特殊な値をCHAR関数で加えたにも関わらず改行されない場合は、恐らく書式設定の問題かと思います。
「改行」という特殊な値を表示させるには、前半の内容と同様に「折り返して全体を表示する」を選択する必要があります。

下記の右側の集計表をスピルを活用して作成する方法について解説していきます。
スピルを活用した数式を作成することで、元の表にデータが追加された場合に関しても、自動で集計することができます。
下記の場合、新しい品名が登録されると、自動で集計表にも追加されるようになっています。

必要な関数

こちらの集計表を作成するにあたり、必要な関数は以下の２つになります。

・UNIQUE関数

=UNIQUE(配列, [列の比較], [回数指定])
// 重複しない唯一のデータを抽出
// 配列：抽出する元データの範囲または配列を指定
// [列の比較]：元データから列方向の値を返したい場合は「FALSE」、行方向の値を返したい場合は「TRUE」を指定
// 省略時は「TRUE」が指定される
// [回数指定]：元データの中で重複していない値のみを抽出する場合は「TRUE」、重複を除いた全ての値を抽出する場合は「FALSE」を指定
// 省略時は「FALSE」が指定される

・SUMIF関数

=SUMIF(範囲, 検索条件, [合計範囲])
// 指定範囲内の中で条件を満たしている値のみを合計
// 範囲：検索条件の範囲を指定
// 検索条件：範囲の中から抽出する条件を指定
// [合計範囲]：値を合計する範囲を指定
// 省略時は引数[範囲]で指定した範囲が設定される

重複を除いた品名一覧

まずは、UNIQUE関数を用いて、重複を除いた品名一覧を作成します。
元データをテーブルにしてから行うと、元データの行数の増減に対応した数式が作成しやすいです。

・テーブルの作成

元データの中のセルを選択して、[挿入]タブから[テーブル]を選択し、テーブルにします。

必要に応じて、[テーブルデザイン]タブから[テーブル名]を変更します。

・数式の作成

次に数式を作成します。
テーブルを参照する場合は、「構造化参照」を活用します。
※テーブルの対象の範囲を選択すると、自動で構造化参照になります。

=UNIQUE(管理表[品名])

このように抽出することができました。

数量の合計

では次に、SUMIF関数を用いて、品名「A」に関してのみ数量の合計を求めていきます。

=SUMIF(管理表[品名],F3,管理表[数量])

このように求めることができました。
後はスピルを活用して、一括で全ての品名の数量を求めていきます。

一括で求める場合は、検索条件の範囲を複数行にします。
※スピルにより拡張された範囲は、数式の入力されているセルに「#」を加えることで表現できます。
　「F3:F6」のように指定して求めることもできますが、今後、品名が追加されることを考慮すると、「F3#」で求める方が好ましいです。

=SUMIF(管理表[品名],F3#,管理表[数量])

このように求めることができました。
こちらの数式を一度作成することで、元のテーブルにデータが追加されると同時に、集計されるようになります。

吹き出し図形に関して、下記の図のように三角の幅が広くなりすぎてしまうことがあるかと思います。

この三角のサイズを細かく調整する方法について解説していきます。

各頂点の微調整

各頂点の微調整をするには、図形を選択した後に、[図形の書式]タブの中から[頂点の編集]を選択します。

黒い四角をドラッグすることで、頂点の位置を調整することができます。
また、黒い四角を選択した後に表示される白い四角をドラッグすることで、下記のように曲線を描くこともできます。

注意点として、頂点の編集をした図形は、長方形と同じ扱いになります。
編集する前の図形の場合、テキストを入力すると、テキストエリア内に表示されるかと思います。

しかし、頂点の編集を行うと、長方形としての角から表示されます。
※見やすいように文字の色を黒色にしています。

中央揃えにした場合に関しても、編集後は、全体の長方形に対しての中央揃えになってしまいます。

そのため、何かしら対策をする必要があります。

テキストを入力する場合は、対策として、テキストボックスを別で用意してグループ化すると良いかと思います。
テキストボックスのサイズは、吹き出しのテキストエリアと同じサイズにします。

作成後、吹き出しとテキストボックスの両方を選択し（Shiftキーを押しながらで複数選択）、右クリックのメニューからグループ化します。

この設定を行うことで、吹き出しのサイズを変更した場合に関しても、テキストボックスの位置が固定されるようになります。

最新のExcelでは「スピル」という機能があります。

＞スピルとは

かなり便利な機能なのですが、多くの集計用の関数はスピルに対応しておりません。
例えば、SUM関数です。

SUM関数を使わない場合と使った場合で比較してみます。

・SUM関数を使わない場合

=C3:C6+D3:D6+E3:E6+F3:F6+G3:G6+H3:H6

・SUM関数を使う場合

=SUM(C3:H6)

上記のように、SUM関数でスピルを使って計算しようとすると上手く計算されません。
SUM関数は、引数の範囲全ての値を合計する関数であるため、行単位での計算などができないのです。
他にも、COUNT関数やAVERAGE関数などでも同様です。

SUM関数がスピルでは使えないからと言って、１つ目の例のように、全ての列を加算するのは大変かと思います。
そんな時に活用できる関数が、BYROW（COL）関数とLAMBDA関数になります。

=LAMBDA(引数1, [引数2], [引数3], …, 数式)
// 数式内で呼び出すことができる関数を作成

＞LAMBDA関数の活用例

=BYROW(配列, LAMBDA関数)
// 行で区切られる配列を渡し、1行単位でLAMBDA関数にて計算し、1列の配列で返す

=BYCOL(配列, LAMBDA関数)
// 列で区切られる配列を渡し、1列単位でLAMBDA関数にて計算し、1行の配列で返す

上記の関数を用いて、以下の表の合計をスピルを活用して求める式を作成していきます。

まずは、LAMBDA関数でSUM関数と同じ機能の関数を作成します。

=LAMBDA(x,SUM(x))

後は、このLAMBDA関数の引数xに、B列からH列の範囲の１行単位の配列を渡すことで、セルI3からI6の合計を求めていきます。

=BYROW(C3:H6,LAMBDA(x,SUM(x)))

同じ要領で、セルC7からI7の合計を求めていきます。
こちらは、１列の結果を返す必要があるため、BYCOL関数を用います。

=BYCOL(C3:I6,LAMBDA(x,SUM(x)))

このように求めることができました。
BYROW（COL）関数を用いることで、スピルを活用した数式の実現の幅が広がります。

Excelの数字には、「文字列の数字」と「数値としての数字」があります。
見た目は同じなのですが、SUM関数などを用いた集計時に違いがあります。

以下のように文字列の数字は集計対象外になってしまいます。

数字を数値として入力したつもりが、文字列になってしまう原因の多くは、以下の２つが考えられます。

・表示形式が「文字列」になっているセルに値を入力
・外部のソフトから書き出したExcelファイル

文字列の数字が入力されているセルは、デフォルトの設定ですと、以下のように警告が表示されます。

そのため、通常は簡単に気づくことができるのですが、Excelの設定によっては表示されない場合があります。
警告を非表示にする設定は、Excelのオプション（[ファイル]→[オプション]）より行えます。

文字列の数字を瞬時に数値にする方法については、以下の記事にて解説しています。

＞文字列の数字を一括で数値にする

上記の内容を踏まえ、セルに数値以外が入力されている場合に、自動で背景色を黄色に設定する方法について解説していきます。

セルの値が数値なのか文字列なのかを確認するには、ISTEXT関数が便利です。

=ISTEXT(値)
// 値が文字列の場合にTRUEを返す

使用例は以下になります。

=ISTEXT(A2)

こちらの関数を活用して、条件付き書式を設定することで、自動で文字列のセルを色付けすることができます。

色付け対象の範囲を選択し、[ホーム]タブの中の[条件付き書式]の[新しいルール]を選択します。

条件付き書式の設定画面が表示されたら、[数式を使用して、書式設定するセルを決定]より、以下の数式と書式を設定します。

=ISTEXT(A2)
// 「A2」は選択の基準となっているセル

上記の設定により、以下のように「文字列のセルの背景色」が自動で黄色になります。