ブログトップ

京都ビジネス学院 舞鶴校


該当する行の取り出し

「日報から顧客名ごとに商談内容を表示させたい。」
という質問を受けて、久しぶりに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)

ITバブル時代のインストラクター仲間で
唯一今もお付き合いのあるK様から刺激を受けて…

スケジュール表
31日までない月の
日付の出し方、
色々あると思いますが、
シンプルにできそうなものを作りました。
b0186959_09455959.jpg
1.セルA2とC2にデータの入力規則を使って
 それぞれ「2019~2025」まで
 「1~12」までリストから選択するだけで
 年と月が入るようにします。
(とりあえず7年分)
b0186959_09515926.jpg
b0186959_09512927.jpg
b0186959_09514307.jpg
2.祝日一覧シートを作り
 (ネット上からコピーしました。)
b0186959_09544563.jpg
 それぞれの年の祝日の入っているセルに
 「祝日2019」「祝日2020」「祝日2021」…
 のように名前を定義します。(7年分)
b0186959_09572435.jpg
3.各月の「1日」が入るセルA4に
 式を入れます。
 「=DATE($A$2,$C$2,1)
 2日目からは
  「=IF(A4="","",IF(DAY(A4+1)=1,"",A4+1))
 (もしA4が空白なら空白で返す、
  そうでなければ
  もし、DAY関数でA4の日付の「日」に
  1を足した数が「1」なら空白に
  (各月の1日になる)
  そうでなければ、前日+1
4.この式をA34までコピーします。
5.B4には、
 「=TEXT(A4,"aaa")」の式を入れます。
 セルB34までコピーします。
6.ためしに、「2月」にしてみると
b0186959_10195790.jpg
2月は28日まで出ました。OKですね。

7.次に、
 土曜日は水色で、
 日曜日はピンクで
 祝日は黄色で塗りつぶしが出来るようにします。

8.セル「A4~D34」選択。
9.条件付き書式→数式
 「=WEEKDAY($A4,1)=1
 「=WEEKDAY($A4,1)=7
 (weekday関数の結果が
 1(日曜日)
  ならセルの塗りつぶしをピンク)
 7(土曜日)
  ならセルの塗りつぶしを水色)
 
b0186959_10353071.jpg
10.祝日の場合は、
 「=COUNTIF(INDIRECT("祝日"&$A$2),$A4)>=1
 INDIRECT関数を使うところがミソ!
 INDIRECT関数は指定した文字が表す参照を返します。
 参照セルを文字列で指定できるので便利です。
 (祝日○年という文字列が表すセル参照=定義した名前の範囲。
 の中に、A4以降の日付が1つ以上あれば、黄色で塗りつぶす。)


b0186959_10420657.jpg
3つの条件付き書式ができました。
b0186959_10535388.jpg
 すると、
 土曜日は水色、日曜日はピンク、祝日は黄色
 で塗りつぶしができました。
b0186959_09455959.jpg
ちなみに来年の4月、5月は…
10連休ですね~~(+_+)
b0186959_10580110.jpg
b0186959_10580989.jpg










 






# by school-pc | 2018-12-26 10:58 | PC-School | Comments(3)

表計算部門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)