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は別々のシートと考えて下さい。
No.3ベストアンサー
- 回答日時:
こんばんは!
>過去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
No.2
- 回答日時:
こんな感じで、どうでしょう。
【D27セル】=COUNTIF(OFFSET($C$1,MATCH(D26,$A:$A,0)-1,,5),"〇")
ただし、A7とD26の値は同じである必要があります。
添付の画像では、書式設定により見た目は違いますが、値は同じになっています。
No.1
- 回答日時:
以前の回答を見ていないので、当て外れなことを書いているかもしれませんのでそれを踏まえておいてください。
質問者様のやろうとしていることが、EXCELを使いこなすための練習問題ではなく、実践的な利用として作っているなら、そもそもsheet1とsheet2に分ける必要性がないです。
まず、コンピューターデータを扱うにあたって最も重要なことは「第一次データ表をとことん使いまわすこと」です。
第一次データというのは手入力、または別のデータから初めてそのエクセルシートに入力されたデータの表のことです。
なぜ「一次データ表を使いまわす」かというと「他のシートなどで参照させると、関数が間違っていることに気が付きにくくなる」からで、4月・5月・6月・・・・のように時間ごとにどんどん増えるデータのために、毎月比較表の関数を増やすのは危険のほうが大きいです。
ですからわたしなら、sheet1のみ使い、関数とソート機能などを使って、やりたいことを実現させます。
で
>D27をドラッグして、5月E27以降、自分の引っ張りたい条件範囲にならず困っています。
の回答ですが「セルをドラッグしてコピーする自動入力は、セルを移動した方向に移動した分しか足されないので、ドラッグではできない」です
つまりD26が「=COUNTIF(sheet1!C7:C11, "◯")」だとしたら、次のE27はセル1個しか移動していないので、自動的に「✕COUNTIF(sheet1!D7:D11, "◯")」になるは当然で、それは移動方向がD→E(27行なのは同じ)だからです。これをドラッグの自動入力で任意の数字を指定する方法はありません。
ただ「ドラッグしても指定の行列番号は変えたくない」と言う場合は「$」を前につければドラッグ時に無視されます。
なのでD26を「=COUNTIF(sheet1!$C7:$C11, "◯")」にすればE27にドラッグしても「=COUNTIF(sheet1!$C8:$C12, "◯")」と列番号である「C」は固定したままにできます。
後は手打ちで変更するしかないです。
このようなことをやっているとミスを誘発することになるので「データ表を使いまわす」ことをお勧めするわけです。
もしどうしてもsheet2のような体裁で実績表を作りたいなら、sheet1のデータ票を「関数で参照できる形にする」必要があります。
たとえば各行に「4月」「5月」と入れるようにするのです。
そうするとsheet2につねに同じ関数で出来るようになるはずです。
その関数にはいくつかの方法があるのですが、それを選ぶ前に「sheet1の見栄えはあきらめること」が重要です。1次データ票は後の加工に使いやすいようにしてください。
先ほど書いたように「すべてに○月を入れる」と見にくくなりますが、後の加工を関数で行うには必要ですし、できれば訪問できたかどうかを記載す「砲門実績欄」も○・×のようなテキスト(文字列)ではなく、0・1という数字で居れる方が後で加工しやすくなります。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- その他(Microsoft Office) 従業員増減対応で当番種類の増減対応な当番表 21 2022/07/19 07:30
- その他(プログラミング・Web制作) pythonでクラスで複数のメソッドを利用する方法 2 2022/04/15 04:17
- Excel(エクセル) Excel 売上管理シートに入力した売上データを、日報に自動反映させたいと考えています。 売上管理シ 3 2023/04/29 18:08
- Excel(エクセル) Excelで日報を自動で作成したい 売上管理シートに入力した売上データを、日報に自動反映させたいと考 1 2023/04/29 18:07
- Excel(エクセル) SUMIFSと日付変換 10 2023/04/16 15:38
- Excel(エクセル) フォルダ内のエクセルファイルを開かずにデータ採取する関数式 2 2022/12/22 22:15
- スーパー・コンビニ 「コンビニで エクセルをプリントアウト」することができますか? 8 2022/06/16 15:54
- Excel(エクセル) 【Excel質問】別シートにある複数の同型の表から、同じ行項目にある数字を集計する 4 2023/02/16 00:14
- Visual Basic(VBA) vbaのvlookup関数エラー原因を教えていただけないでしょうか。 3 2022/04/25 16:16
- Excel(エクセル) Excelについて A1からA12まで、1月〜12月と入力し、 B1からB12の範囲に、C1とD1に 4 2022/05/26 22:48
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
【関数】先頭だけにある、半角...
-
エクセル 白黒印刷で白線を印刷...
-
Excelのチェックボックスの使い...
-
【関数】適切な文字数の数字を...
-
Excelのpivotについて質問です
-
Excel ピボットテーブルで日付...
-
LOOKUP関数を使えばいいのでし...
-
エクセル関数を教えてください
-
エクセルのセルに同じ大きさの...
-
UNIQUE関数が使えないバージョ...
-
excelの不要な行の削除ができな...
-
エクセルで「-0.0」と表示さ...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの関数について教えて...
-
Aというブックの1というシート...
-
【マクロ】シート名を取得する...
-
VBA Private Sub Worksheet_Cha...
-
VBA、Excelのworkbook.open に...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報
ちなみに…。
過去2回COUNTIFにOFFSETやCOLMNを組み合わせた数式を回答して下さった方もいましたが、何故そうなるか理解できなかったため、解説して下さると有難いです。