Excel データのコピーの方法 これで3回目の質問です。 初心者につきよろしくお願いします。 ある

Excel データのコピーの方法
これで3回目の質問です。
初心者につきよろしくお願いします。

あるお客様データです。
各月に商品を購入頂いた方に対し、満足調査のため、訪問を実施できたかどうかの確認表です。

sheet1、
各月5名までのお客様さんの氏名が入力できます。
4月B7～B11、5月B12～B16、6月B17～B21
月によって5人の時もあれば、1人の月もあります。
5人とした理由は過去1年間の月単位の最大購入者数が5人だったためです。月によっては1人の時もあります。よって空白セルも発生します。月ごとにデータをとっていくためにこのような表にしました。
C列は訪問実績として、訪問できたかできなかったかをデータとしてとるための列です。
リストを作成し、
◯・・・実施できた
✕・・・実施できなかった
選択できるようにしました。

sheet2
各月の実績が一目で分かる表です。
sheet1で、4月C7:C11に訪問できた◯の人数が入るセルはD27です。
=COUNTIF(sheet1!C7:C11, "◯")で問題なくカウントできました。
問題はここからです。
D27をドラッグして、5月E27以降、自分の引っ張りたい条件範囲にならず困っています。
✕引っ張られた数式
◯自分がしたい数式
とすれば
5月E27は
✕COUNTIF(sheet1!D7:D11, "◯")
◯COUNTIF(sheet1!C12:C16, "◯")
6月F27は
✕COUNTIF(sheet1!E7:C11, "◯")
◯COUNTIF(sheet1!C17:C21, "◯")
7月以降も横ではなく、縦に引っ張っていきたい。

一つの解決策として、sheet1は年月が縦、sheet2は年月が横に並べられているため、統一すれば良いと思いますが、そうしなくてもできる方法を知りたいです。

sheet1とsheet2は本来別々のsheetですが、写真を1枚に撮るため、同じsheetに入力しています。sheet1とsheet2は別々のシートと考えて下さい。

質問者からの補足コメント

• ちなみに…。
  過去2回COUNTIFにOFFSETやCOLMNを組み合わせた数式を回答して下さった方もいましたが、何故そうなるか理解できなかったため、解説して下さると有難いです。

    補足日時：2020/03/14 18:31

No.3 ベストアンサー 回答者： tom04 回答日時： 2020/03/14 20:35

こんばんは！

＞過去2回COUNTIFにOFFSETやCOLMNを組み合わせた数式を回答・・・
↓のサイトですね。

https://oshiete.goo.ne.jp/qa/11525218.html

COUNTIF関数の範囲を指定するのにOFFSET関数を使って、右へフィル＆コピーするとなぜ範囲がずれるのか？
を理解したいと解釈しましたので、簡単に説明すると
まずOFFSET関数から説明が必要だと思います。

OFFSET(参照,行数,列数,高さ,幅)
のような順になります。
参照　→　基準になるセル
行数　→　参照セルから何行下か
列数　→　参照セルから何列右か（マイナスなら左か）
高さ　→　セル数（行数）
幅　→　セル数（列数）

といった順になります。

↓の画像では説明しやすいようにC1セルに
=OFFSET($A$1,(COLUMN(A1)-1)*3,,1)
という数式を入れ、右へフィル＆コピーしています。

ここで判りにくい部分といえば「COLUMN(A1)-1)*3」だと思います。
どこか使っていないセルに
=COLUMN(A1)-1)*3

という数式を入れフィルハンドルで右へコピーしてみてください。
0・3・6・9・・・と表示されるはずです。
この部分が「行数」になり、A1セルから何行下か？となります。

※　列数の部分は何も入力していないので、「0」になり、
高さ　→　「1」　としているので黄色いセルのみが表示される
最後の「幅」も省略しているので1列のみ！

上記サイトの場合はCOUNTIF関数の「範囲」でOFFSET関数を使っているので
最後の「1」の部分が「3」（3行分）としています。

お示しの画像の配置だと「○」をカウントする場合は
=COUNTIF(OFFSET(Sheet1!$C$7,(COLUMN(A1)-1)*5,,5),"○")

という数式を入れ右へフィル＆コピーすれば大丈夫だと思います。

※　前回の数式と今回の数式の違いは理解してもらえますよね。
（前回は3行の範囲だったのが、今回は5行を範囲にしている）

尚、COUNTIF関数の数式内に　○　という「検索条件」をそのまま入れていますが、
どこかのセルに「検索条件」があれば、そのセルを指定した方が汎用性があります。

以上、長々と失礼しました。m(_ _)m

この回答へのお礼

やっとできました！！
ありがとうございます。

お礼日時：2020/03/14 21:19

No.2 回答者： ママチャリ 回答日時： 2020/03/14 20:15

こんな感じで、どうでしょう。

【D27セル】=COUNTIF(OFFSET($C$1,MATCH(D26,$A:$A,0)-1,,5),"〇")

ただし、A7とD26の値は同じである必要があります。
添付の画像では、書式設定により見た目は違いますが、値は同じになっています。

この回答へのお礼

そんな方法もあるんですね
有難うございます。

お礼日時：2020/03/14 20:40

No.1 回答者： phj 回答日時： 2020/03/14 18:52

以前の回答を見ていないので、当て外れなことを書いているかもしれませんのでそれを踏まえておいてください。

質問者様のやろうとしていることが、EXCELを使いこなすための練習問題ではなく、実践的な利用として作っているなら、そもそもsheet１とsheet２に分ける必要性がないです。

まず、コンピューターデータを扱うにあたって最も重要なことは「第一次データ表をとことん使いまわすこと」です。
第一次データというのは手入力、または別のデータから初めてそのエクセルシートに入力されたデータの表のことです。

なぜ「一次データ表を使いまわす」かというと「他のシートなどで参照させると、関数が間違っていることに気が付きにくくなる」からで、4月・5月・6月・・・・のように時間ごとにどんどん増えるデータのために、毎月比較表の関数を増やすのは危険のほうが大きいです。

ですからわたしなら、sheet１のみ使い、関数とソート機能などを使って、やりたいことを実現させます。

で
＞D27をドラッグして、5月E27以降、自分の引っ張りたい条件範囲にならず困っています。
の回答ですが「セルをドラッグしてコピーする自動入力は、セルを移動した方向に移動した分しか足されないので、ドラッグではできない」です

つまりＤ26が「=COUNTIF(sheet1!C7:C11, "◯")」だとしたら、次のＥ27はセル1個しか移動していないので、自動的に「✕COUNTIF(sheet1!D7:D11, "◯")」になるは当然で、それは移動方向がD→Ｅ（27行なのは同じ）だからです。これをドラッグの自動入力で任意の数字を指定する方法はありません。

ただ「ドラッグしても指定の行列番号は変えたくない」と言う場合は「＄」を前につければドラッグ時に無視されます。
なのでD26を「=COUNTIF(sheet1!$C7:$C11, "◯")」にすればE27にドラッグしても「=COUNTIF(sheet1!$C8:$C12, "◯")」と列番号である「Ｃ」は固定したままにできます。
後は手打ちで変更するしかないです。

このようなことをやっているとミスを誘発することになるので「データ表を使いまわす」ことをお勧めするわけです。

もしどうしてもsheet2のような体裁で実績表を作りたいなら、sheet１のデータ票を「関数で参照できる形にする」必要があります。

たとえば各行に「4月」「5月」と入れるようにするのです。
そうするとsheet２につねに同じ関数で出来るようになるはずです。

その関数にはいくつかの方法があるのですが、それを選ぶ前に「sheet１の見栄えはあきらめること」が重要です。1次データ票は後の加工に使いやすいようにしてください。
先ほど書いたように「すべてに○月を入れる」と見にくくなりますが、後の加工を関数で行うには必要ですし、できれば訪問できたかどうかを記載す「砲門実績欄」も○・×のようなテキスト（文字列）ではなく、0・1という数字で居れる方が後で加工しやすくなります。

この回答へのお礼

セルを移動した方向に移動した分しか足されない
やっぱりそうなんですね。
有難うございます。

お礼日時：2020/03/14 20:00