忍者ブログ

Excel.sys

Excelでどこまでできるか! ExcelとStiLLでビジネス・デザイン自由自在!!

   

関数で項目をグループ化する

シート上の明細をグループ化(集約)する便利な関数式を伝授!

◆こんな時
複数行の明細から顧客名のみを取り出してグループ化したい!

◆解決ポイント
関数を組み合わせることで実現できます。

◆使用方法
先ず考え方として下図のイメージを参考にします。
  
Sheet1の明細から顧客名をグループ化してSheet2に表示させます。

A列は事前に顧客名でソートしておきます。

Sheet2は以下のイメージです。


式の作り方として、始めにSheet2のA列の見出し"顧客名"を
Sheet1のA列で探す式を考えると以下のようになります。
 =MATCH(Sheet2!A1,Sheet1!A:A,0)

この式をSheet2のセルA2に入力します。
常に1行上の(顧客名)をもとに次の(顧客名)を取得する式とします。
以下、セルA2に入力する式について述べていきます。

”顧客名”はSheet1のセルA1にあるのでこの式の結果は1(行目)です。

もし、Sheet2のリストの見出しを"お客様"とした場合はSheet1には無いので
この式はエラーになります。

そこで無いときのことを考えてIFERRORで対処します。
エラーのとき最初に取得したい位置はSheet1の2行目の(顧客名)ですので
次のような式にしておきます。
 =IFERROR(MATCH(Sheet2!A1,Sheet1!A:A,0),2) ・・・(a)

これでSheet2の見出しがSheet1に無い場合でも2(行目)となります。
上記の式で求めた結果はSheet1のA列の行数を示しています。

この結果をINDEX関数に使うことで(顧客名)を得ることができます。
 =INDEX(Sheet1!A:A,IFERROR(MATCH(Sheet2!A1,Sheet1!A:A,0),2))
※この式の意味は、Sheet2のA1に設定した(顧客名)がSheet1のA列に
 存在する場合はその値を、無い場合は強制的にSheet1のA2の値を取得
 するものです。

ここまでで基本的な式は記述できましたが、考慮しなければいけないもの
があります。それは同じ(顧客名)が複数行あるということです。

そこで何行分同じ(顧客名)があるかをCOUNTIF関数を使って取得します。

例えばこの例では「喫茶たいむましん」が2行ありますが、
次の式で何行あるかを取得します。
 =COUNTIF(Sheet1!A:A,Sheet2!A1) ・・・(b)

大事な解説:
(a)の式は、1行上の(顧客名)がSheet1のA列の何行目にあるかを取得し、
(b)の式は、その(顧客名)が何行あるかを取得します。
そして、この2つの式の値を足した結果が次の(顧客名)の開始行になります。

したがって、最終的にセルA2に入力する式は次のようになります。
 =INDEX(Sheet1!A:A,IFERROR(MATCH(Sheet2!A1,Sheet1!A:A,0),2)
  +COUNTIF(Sheet1!A:A,Sheet2!A1))&""
最後に""を結合して、空白の場合に0が表示されないようにしています。

このセルA2の式を、A3以降にドラッグしていくと、見事に(顧客名)が
グループ化され表示されます。(以降は空白となります)


◆「StiLL」のコマンドボタンについて
「StiLL」はExcelを便利にアップグレードするアドインツールです。

「セル値セット(BtSetValue)」に今回の式を設定しておけばデータ量に
関わらず一瞬で明細をグループ化できます。
しかも式ではなく、値として結果を得られます。

これをあなた自身で構築することができるのです。
さらにこの処理(動作)を自動化できるのです!

StiLL はすぐ使える。(いいね!)

ー 仕事に趣味に ー
StiLL はEXCELのベストパートナー

体験セミナー実施中!(無料)

それではまた来週。
PR

お知らせ

「StiLL体験セミナー」
(東京 恵比寿)
!!NEW!!
 StiLL体験セミナー

日付:2020年2月4日(火)
時間:14:00~17:00
場所:アイエルアイ総合研究所
   
参加費:無料(定員8名)
詳細・お申込:こちらから

ブログ内検索

Copyright ©  -- Excel.sys --  All Rights Reserved
Design by CriCri / Photo by Geralt / powered by NINJA TOOLS / 忍者ブログ / [PR]