[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『2つのシートに分かれているデータで、横向きのデータから日付範囲で抽出』(たまこ)
2つシートがあり、参加日シートには名前と参加日のリスト、連絡日シートは名前と連絡日の表があります。
名前、参加日、連絡日は増加していきます。
参加日シートでは、名前(A列)は重複して存在します。
たとえば、Aさんが1月2日と5月3日に参加していると、Aさんの行は2行存在しています。
連絡日シートでは、名前(A列)に重複はなく、連絡日が名前(A列)の隣の列へリストされています。
例えば、Aさんへの連絡日は2月12日と4月1日であれば、B列とC列に連絡日が記載されています。このとき、連絡日の横並びの順番は順不同です。
参加日シート
A B C D E 1 名前 参加日 30日前の日付 連絡日シートの行番号 連絡の有無 2 Aさん 2021/1/2 2020/12/3 2 3 Bさん 2021/4/3 2021/3/4 3 4 Cさん 2021/2/13 2021/1/14 4 〇 5 Aさん 2021/5/3 2021/4/3 2 6 Dさん 2021/4/5 2021/3/6 5 〇 7 Eさん 2021/1/2 2021/12/3 6 8 Cさん 2021/3/25 2021/2/23 4
連絡日シート
A B C 1 名前 連絡日1 連絡日2 2 Aさん 2021/4/1 2021/2/12 3 Bさん 2021/1/3 2021/7/15 4 Cさん 2021/6/26 2021/1/31 5 Dさん 2021/3/20 6 Eさん 2021/2/28
教えていただきたいのは、参加シートE列の計算式です。
Aさんが参加した日から遡って30日以内(参加した日を含む)に、Aさんに連絡しているかどうかを表記したい。(連絡している場合は〇)
連絡日シートでのAさんの行番号を特定(D列 計算式有り)して、参加日シートのE列に下記の計算式を入れました。
=IF(COUNTIFS(連絡日!2:2,"<="&参加日!B2,連絡日!2:2,">="&参加日!C2),"〇","")
この計算式では、連絡日!2:2の部分を一つ一つ変更する必要があります。
サンプルでは名前7件ですが、実際のデータでは既に5千件超ありますし、連絡日の日付も、まだまだ増えていきます。このままの計算式では非実用的で全く使い物になりません。
といっても、私の考えられるところではここまでが限界で・・・・・
コピペで、もっとスマートに自動計算できるように教えていただけますよう、お願いいたします。
< 使用 Excel:Excel2013、使用 OS:Windows10 >
参加日シートのC列とD列は、例示通りに表示されているのなら
E2 =IF(COUNTIFS(INDEX(連絡日!B:C,D2,0),"<="&B2,INDEX(連絡日!B:C,D2,0),">="&C2),"○","") 下コピー
例示では〇(漢数字のゼロ)になっていますが、○(丸印)にしました。 漢数字のゼロでないとマズいのなら変更してください。
以上 (笑) 2021/09/02(木) 16:37
おお!できました! ありがとうございます。
欲を言うと・・・
連絡日!B:Cの部分ですが、連絡日もどんどん増えていくので、Cで固定しない方法はありますでしょうか?
もしくは、最大AZまでとか決めて、B:AZにするしかないでしょうか?
記号○ もちろんOKです。漢数字、失礼しました。
(たまこ) 2021/09/02(木) 19:01
連絡日シートの表を「テーブル」に変換すれば、データを追加しても(右方向にも下方向にも) 数式も自動的に追加した範囲になると思いますけど・・・ ※2021/09/02(木) 16:37 の数式を少し変更する必要あり
または >もしくは、最大AZまでとか決めて、B:AZにするしかないでしょうか? これではダメなんですか?
INDEX(連絡日!B:C,D2,0) ↓ INDEX(連絡日!B:AZ,D2,0)
こうするってことですよね?
以上 (笑) 2021/09/02(木) 22:37
数式も自動的に追加した範囲になると思いますけど・・・
なるほど! と思ってやってみたのですが…
連絡日シートの表を 連絡日テーブル にして、参加日シートの方で
=IF(COUNTIFS(INDEX(連絡日テーブル,D2,0),"<="&B2,INDEX(連絡日テーブル,D2,0),">="&C2),"○","")
と入れて下にドラッグしました。
○が入っていたものが全部空白になりました。
テーブルの指定方法を間違えているのでしょうか?
何度も何度も申し訳ございません。
INDEX(連絡日!B:AZ,D2,0) こうするってことですよね? そうです。
大丈夫といえば大丈夫です。
万が一データが増えた時に、計算式の変更を私が忘れなければよいだけの話ですね。
(たまこ) 2021/09/03(金) 10:12
>=IF(COUNTIFS(INDEX(連絡日テーブル,D2,0),"<="&B2,INDEX(連絡日テーブル,D2,0),">="&C2),"○","")
D2 → D2-1 にしたらどうなりますか? テーブルの範囲に1行目の見出しは含まれていないようなので
=IF(COUNTIFS(INDEX(連絡日テーブル,D2-1,0),"<="&B2,INDEX(連絡日テーブル,D2-1,0),">="&C2),"○","") ~~~~ ~~~~ 以上 (笑) 2021/09/03(金) 11:37
本当にありがとうございました。
何度も何度も申し訳ございませんでした。
(たまこ) 2021/09/03(金) 18:16
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.