Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(指定なし : Excel 2013)
項目の文字を検索し、列の位置を取得する
投稿日時: 22/01/11 15:58:10
投稿者: なみっこ

日次シートは、元データシートのデータを参照していています。
日次シートD2=countifs(元データシート!$A:$A,日次シート!D1,元データシート!$C:$C,"りんご")
  
数ヶ月に1度、元データシートの項目が追加されたり、列の順番が変わったりするため
そのたびに関数の編集を行っています。
元データシートの1行目から、項目の文字列をを検索し位置を取得する方法はないでしょうか。
取得した列番号をcountifsの検索条件範囲に指定したいです。
  
  
  
〜〜日次シート〜〜
  
  B列  C列  D   E  F
1        1/1  1/2 1/3 1/4
2 A店  りんご 0 
3     みかん 0
4 B店 りんご 1
5     みかん 0 1
6 C店  りんご 1
7     みかん 0
  
  
〜〜元データシート〜〜
  A列  B列  C列    D   E  F  XL
1 日付  店舗  商品  天気  人数 … 期限
2 1/1   B店  りんご 晴れ  3     1/12 
3 1/1   C店  りんご 晴れ  3
4 1/2   B店  みかん
  
説明が下手ですみません。
ご教授頂けますと幸いです。

回答
投稿日時: 22/01/11 16:30:37
投稿者: んなっと

元データシートの表を「テーブル」に変換するのはどうでしょうか。
 
仮に「テーブル1」という名前になったなら...
 
   B    C  D  E
1        1/1 1/2
2 A店 りんご  0  0
3    みかん  0  0
4 B店 りんご  1  0
5    みかん  0  1
6 C店 りんご  1  0
7    みかん  0  0
 
D2
=COUNTIFS(テーブル1[[日付]:[日付]],D$1,テーブル1[[店舗]:[店舗]],LOOKUP("ーー",$B$2:$B2),テーブル1[[商品]:[商品]],$C2)
右方向・→下方向・↓

回答
投稿日時: 22/01/11 17:17:07
投稿者: んなっと

テーブルにする方法は、
 
excel テーブル
 
でGoogle検索してください。

回答
投稿日時: 22/01/11 18:22:58
投稿者: WinArrow
投稿者のウェブサイトに移動

>日次シートD2=countifs(元データシート!$A:$A,日次シート!D1,元データシート!$C:$C,"りんご")
この数式には、日付が加味されていませんね・・・
 
SUMPRODUCT関数を使用する案
 
日次シートの
セルD2に
=SUMPRODUCT((元データシート!$B:$B=$B2)*(元データシート!$C:$C=$C2)*(元データシート!$A:$A=D$1))
下、右へフィルドラッグします。
 

VBAの板へもレスしましたが、
具体的なシートが掲示されていないので、
適当なレスをしてしまいました。
VBAのスレは、閉じてください。

回答
投稿日時: 22/01/11 18:43:02
投稿者: WinArrow
投稿者のウェブサイトに移動

注意事項を忘れました。
 
日次う\シートのB列セル
空白セルが無いようにしてください。

回答
投稿日時: 22/01/11 18:47:51
投稿者: んなっと

引用:
数ヶ月に1度、元データシートの項目が追加されたり、列の順番が変わったりするため
そのたびに関数の編集を行っています。
元データシートの1行目から、項目の文字列をを検索し位置を取得する

という要求をそのまま反映させると、こんなに長くなってしまいます。
 
=COUNTIFS(INDEX(元データシート!$1:$900,,MATCH("日付",元データシート!$1:$1,0)),D$1,INDEX(元データシート!$1:$900,,MATCH("店舗",元データシート!$1:$1,0)),LOOKUP("ーー",$B$2:$B2),INDEX(元データシート!$1:$900,,MATCH("商品",元データシート!$1:$1,0)),$C2)
 
ほかに名前定義を使う方法があるかもしれません。

回答
投稿日時: 22/01/11 21:41:31
投稿者: WinArrow
投稿者のウェブサイトに移動

WinArrow さんの引用:
注意事項を忘れました。
 
日次う\シートのB列セル
空白セルが無いようにしてください。

 
入力ミスがりました。
 
再度、真意を掲示します。
 
日次シートのB列(店舗名)のンかに空白のセルがあります。
SUMRODUT関数では、空白セルの個所は不一致となってしまうので、
空白セルが無いように入力してください
 

回答
投稿日時: 22/01/12 10:36:14
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:

元データシートの1行目から、項目の文字列をを検索し位置を取得する方法はないでしょうか

 
↑の回答
MATCH関数を使えば、列位置を取得可能です。

=MATCH("店舗",元データシート!$A$1:$Z$1,0)
これで取得した値をINDEX関数の列方向に適用します。

回答
投稿日時: 22/01/12 11:00:11
投稿者: WinArrow
投稿者のウェブサイトに移動

↑MATCH関数取得値の利用方法
 
例:日次シートのセルD2へ
=COUNTIFS(INDEX(元データ,,日付列),D$1,INDEX(元データ,,店舗列),$B2,INDEX(元データ,,商品列),$
C2)
 
この数式では、セル範囲に名前定義しています。
元データシートのデータ範囲(1行目を除く)に「元データ」という名前を定義
 
日次シートの任意のルに
日付列
店舗列
商品列
という名前を定義し、各々にMATCH関数を入力します。
 

回答
投稿日時: 22/01/12 15:00:04
投稿者: WinArrow
投稿者のウェブサイトに移動

おまけ
 
>元データシートのデータ範囲(1行目を除く)に「元データ」という名前を定義
方法
名前定義の参照範囲・・・データ件数を可変にする方法
=OFFSET(元データシート!$A$1,1,0,COUNTA(元データシート!$A:$A),COUNTA(元データシート!$1:$1))

トピックに返信