HOME > Q&A > Excel （一般機能）

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

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

日次シートは、元データシートのデータを参照していています。
日次シートD2=countifs(元データシート!$A:$A,日次シート!D1,元データシート!$C:$C,"りんご")
  
数ヶ月に１度、元データシートの項目が追加されたり、列の順番が変わったりするため
そのたびに関数の編集を行っています。
元データシートの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
投稿者のウェブサイトに移動

注意事項を忘れました。
 
日次う\シートのＢ列セル
空白セルが無いようにしてください。
回答
投稿日時: 22/01/11 18:47:51
投稿者: んなっと

引用:
数ヶ月に１度、元データシートの項目が追加されたり、列の順番が変わったりするため
そのたびに関数の編集を行っています。
元データシートの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 さんの引用:
注意事項を忘れました。
 
日次う\シートのＢ列セル
空白セルが無いようにしてください。

 
入力ミスがりました。
 
再度、真意を掲示します。
 
日次シートのＢ列（店舗名）のンかに空白のセルがあります。
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)
 
この数式では、セル範囲に名前定義しています。
元データシートのデータ範囲(１行目を除く）に「元データ」という名前を定義
 
日次シートの任意のルに
日付列
店舗列
商品列
という名前を定義し、各々にMATCH関数を入力します。
 
回答
投稿日時: 22/01/12 15:00:04
投稿者: WinArrow
投稿者のウェブサイトに移動

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