ブログトップ

京都ビジネス学院 舞鶴校

カテゴリ:未分類( 892 )


該当する行の取り出し

「日報から顧客名ごとに商談内容を表示させたい。」
という質問を受けて、久しぶりにimogasi方式で抽出したいと思います。

顧客名ごとに別シートに関数で表示させます。

以前のblog記事 Excelワザ_複数データの取り出し https://schoolpc.exblog.jp/22907048/

でも書いたのですが、もう一度復習を兼ねて。

よねさんのWordExceの部屋→関数の技

エクセル関数の技:関数で複数データを取り出す http://www.eurus.dti.ne.jp/~yoneyama/Excel/waza/fukusu_data.html

このサイトの説明がよくわかってよいと思います。よねさんもimogasi氏もすごい!


日報から

b0186959_18452416.jpg
顧客名ごとに商談日時・内容を表示
b0186959_18452942.jpg
手順

1.まず、顧客名簿作成

b0186959_18500444.jpg

例として10件分

A1に№

 A2には 式 =IF(B2="","",ROW()-1)

  もしB2が空白なら空白、
  そうでなければ、ROW関数で行番号2を返し-11が返る。

  この式をA3からA11までオーフィルでコピー

C列~I列まで、それぞれ、顧客名~担当者名まで入力。

 (よみがなは株式会社、有限会社を省いた読みを基準に並べ替えるため)

③入力し終えたら、並べ替えで、よみがな順に昇順で並べ替える。

B列に顧客番号10011010までオートフィルで入力。

名前を定義します。

 顧客名が増減しても対応できるように、OFFSET関数を使って、範囲を可変にします。

b0186959_18453912.jpg
 =OFFSET関数:OFFSET(基準,行数,列数,高さ,)

=OFFSET(基準となるセル,基準からいくつ縦にずれるか, 基準からいくつ横にずれるか,範囲の行数,範囲の列数)

範囲名→顧客名:=OFFSET(顧客名簿!$C$2,0,0,COUNTA(顧客名簿!$C:$C)-1,1)

  1件目の顧客名が入っているC2を基準セルとし、移動する(縦・横にずれる)行、列は「0

  高さ(範囲の行数)は「COUNTA$C:$C-1」を指定します。
  これはCOUNTA関数でC列に入力されたセルの数を数え、見出し行分を含めないように1を引きます。

  幅(範囲の列数)は顧客名だけなので、「1」を指定。

b0186959_18454977.jpg


同様に顧客番号も定義しておきます。

範囲名→顧客番号:=OFFSET(顧客名簿!$B$2,0,0,COUNTA(顧客名簿!$B:$B)-1,1)

(日報シートで、リストから選択するだけで顧客番号が入力できるようにするため)

b0186959_18454434.jpg
次に、顧客番号から顧客名をVLOOKUP関数で出したいので、顧客名簿も定義します。
範囲名→顧客名簿:=OFFSET(顧客名簿!$A$2,0,0,COUNTA(顧客名簿!$A:$A)-1,9)

(列数はA列からI列まで9列なので、「9」)

b0186959_18455537.jpg
更に、作業列も名前定義しておきます。
範囲名→作業列:=OFFSET(日報!$G$2,0,0,COUNTA(日報!$G:$G)-1,1)

b0186959_18460232.jpg

2.次に日報シートの作成

b0186959_18461097.jpg
A列:№

 A2に、式:=IF(B2="","",ROW()-1)を立てておく。

 (もし、B2が空白なら空白で返し、そうでなければ、

  ROW関数でB2の行数2-11が返るようにしておく。)

  この式を下方向にオートフィルでコピー

b0186959_18461773.jpg
B列:月日

 B2以降は商談月日を入力。○月○日表示で。

C列:曜日

 C2に、式:=TEXT(B2,"aaa")を立て、

 曜日が表示されるようにする。

 この式を下方向にオートフィルでコピー

b0186959_19082805.jpg

D列:顧客番号

 D列選択→データ→データの入力規則→設定

 →入力値の種類:リスト→元の値:=顧客番号

 と設定。すると、∇から選択できるようになります。

b0186959_19084272.jpg
b0186959_19085412.jpg

E列:顧客名

 E2に、式=IF(D2="","",INDEX(顧客名簿,MATCH(D2,顧客番号,0),3))

 これで、顧客番号に対応する顧客名が出ます。

 この式を下方向にオートフィルでコピー

b0186959_19090037.jpg

F列:商談内容は、入力します。

3.集計シートの作成

b0186959_19090686.jpg

B1に取引先名

 C1D1を結合し、

 リストから顧客名が入るようにします。

 データ→データの入力規則→設定

 →入力値の種類:リスト→元の値:=顧客名

 →リストから選択すると顧客名が入ります。

b0186959_19091203.jpg
b0186959_19091744.jpg

4.日報シートに戻ります。

G列:作業列

 G2に式=IF(E2=集計シート!$C$1,COUNTIF($E$2:E2,集計シート!$C$1),"")

 条件と一致した行に連番を入れる式です。

 E2の顧客名と集計シートのC2の顧客名が同じなら、
 COUNTIF関数でE2からE列を数えて、何個目か(何個あるか)
 を表示します。同じでないなら、空白)

 この式を下方向にオートフィルでコピーします。

 すると、一致する顧客名の作業列に1,2,3,…と連番が振られます。

b0186959_19092304.jpg
b0186959_19092956.jpg

5.この連番を利用して、

①集計シートの月日、曜日、商談内容を表示させます。


B4に式:=IF(MAX(作業列)<ROW(日報!B1),"",INDEX(日報,MATCH(ROW(日報!B1),作業列,0),2))

 ・(INDEX関数で検索するセル範囲は、名前定義した日報の範囲

 ・作業列の1,2,…を探すのは、MATCH(ROW(日報!B1),作業列,0)の部分で、

 ・検査値:ROW(日報!B1)で、「1」と完全一致する行を作業列の範囲で探します。

  (ROW(日報!B1)を使うのは、ROW関数の返す値(=行番号の数字)を下方向へコピーし、

   連番を出させるため。1,2,3…のように)

   MATCH関数で完全一致する行を求め、日報範囲の2列目の値を出します。→月日

 ・IF(MAX(作業列)<ROW(日報!B1),""の部分は

  (連番より大きくなると、一致するデータはないことになるので、エラー表示させないように、

  一致するデータ数を超える行は空白になるようにします。

b0186959_19093330.jpg

 同様に

 ③C4に式:=IF(MAX(作業列)<ROW(日報!C1),"",INDEX(日報,MATCH(ROW(日報!C1),作業列,0),3))

 ④D4に式:=IF(MAX(作業列)<ROW(日報!D1),"",INDEX(日報,MATCH(ROW(日報!D1),作業列,0),6))

  INDEX関数の列を3、にすると、日報の範囲の3列目→該当する月日が表示されます。

  6にすると、日報の範囲の6列目→該当する商談内容が表示されます。



 ⑤集計シートの

  №列のA4の式=IF(B4="","",ROW()-3)

 (隣のセルB4が空白なら空白で返し、
  そうでなければ、ROW関数で行4-31を返す。

  この式を下方向にオートフィルでコピーします。

b0186959_19093854.jpg
これで、集計表の取引先名を選択すると、
日報から、商談日時、商談内容が表示されます。

b0186959_19095042.jpg

b0186959_19094418.jpg















by school-pc | 2019-03-14 17:33 | Comments(0)

Windows7サポート


2020/1/14
ですねぇ(-_-;)
ちょいちょい聞かれるので…

うちのPC達は
無償アップグレード期間に
10にしておいたから、OKです(^_-)-☆
皆さんはどうかしら?(;^_^A

by school-pc | 2019-02-12 14:03 | Comments(0)

表計算部門3級 1名
ワープロ部門3級 1名
合格!!
お疲れさまでした。


by school-pc | 2018-12-02 23:44 | Comments(0)

12月度試験

今回は、CS検定
表計算、ワープロ部門とも
3級1名ずつ
頑張れ!

by school-pc | 2018-11-26 12:33 | Comments(0)

MOS2016 合格!

MOS2016 Excel・Word
b0186959_12304835.jpg
京都のPCスクールでとってきました。
年内中に行って来られて良かった(^_-)-☆
もうこれが最後かなぁ…(^-^;

by school-pc | 2018-11-25 23:23 | Comments(0)

長浜市 鶏足寺

滋賀県長浜市 鶏足寺の紅葉
b0186959_12224423.jpg
b0186959_12224984.jpg

by school-pc | 2018-11-16 23:21 | Comments(0)

AVERAGE関数は、平均を出すとき、
「0」のセルも入ってしまいます。
「0」を除いた平均を求めるときは、
AVERAGEIF関数で求めます。
b0186959_09171496.jpg


b0186959_10010400.jpg
「平均点1」は、
=AVERAGE(C2:C11)で、「62.7」点。
内容は、=SUM(C2:C11)/COUNT(C2:C11)
合計を数字の入っているセルの数で割る答(0も入っている)

0を除いた平均を求める式は
=AVERAGEIF(C2:C11,"<>0")
内容は、=SUM(C2:C11)/COUNTIF(C2:C11,"<>0")
合計を0以外のセルの数で割る答(0は除かれる)

b0186959_09563682.jpg
結果が随分変わるね。



by school-pc | 2018-11-13 09:24 | Comments(0)

すかし百合

冷凍の球根を頂き、今頃開花。
綺麗な色。
匂いもないし、不思議。
b0186959_11160756.jpg

by school-pc | 2018-11-06 11:15 | Comments(0)

雲海

大江山の雲海が少しだけ見えました。
地頭の高速入口付近
b0186959_11143847.jpg

by school-pc | 2018-11-04 11:13 | Comments(0)

上高地、乗鞍

素晴らしい!
絶景でした。
b0186959_11114856.jpg
b0186959_11115782.jpg
b0186959_11121252.jpg
b0186959_11121823.jpg
b0186959_11123291.jpg
b0186959_11123946.jpg


by school-pc | 2018-10-23 11:10 | Comments(0)