Excel関数について教えてください

質問

会社でほかの人が添付ファイルのような、Excel作成したのですが、赤文字にしているA列とE、F列を関数で表示するようにしたいです。
A列はVlookup用に名前を検索して名前を連続させたいです。ここでは地名を例で記入してます。
E、F列は右の線表から日付を抽出したいのですが、どなたかご教授願えませんでしょうか。

回答No.1

kkkkkm · ベストアンサー

C列が2行目から5行ごとワンブロックだとして
A2に
=IF(MOD(ROW(),5)=2,C2,A1)
で下にコピーしてください。
E3に
=IFERROR(INDEX($G$1:$AJ$1,1,MATCH("着工",$G3:$AJ3,0)),"")
F3に
=IFERROR(INDEX($G$1:$AJ$1,1,MATCH("完工",$G3:$AJ3,0)),"")
として下にコピーしてください。AJ3は実際にデータのある右端の列を指定してください。書式はG1に合わせてください。

お礼

迅速な解答ありがとうございます。シンプルでとても分かりやすくできました。ありがとうございました！

回答No.7

chayamati

1.記入箇所リストを別sheet(sheet名：索引）のA列に記入します。
2.A列に入力規則で索引リストを適用します
　列名Aをクリック
　　全てのA列が対象になります
　→▼データリボン→▼入力規則→▼データの入力規則→▼設定→▼入力値の種類のプッルダウン→リスト→元の値
　→検索Sheet→A2セル→Shift+リストの最終行の2行したのセル→Ok
3.EF列に入力規則でG1～T1(31日分）を適用します
　列名EとFを捉まえる
　　全てのE,F列が対象になります
　→▼データリボン→▼入力規則→▼データの入力規則→▼設定→▼入力値の種類のプッルダウン
　→リスト→元の値
　→G1セル→Shift+T1セル→Ok

お礼

ご回答ありがとうございます。入力書式を使用することは思いつきませんでした。試してみます。ありがとうございました。

回答No.6

msMike

A2: =OFFSET(C$2,INT((ROW(A1)-1)/5)*5,)

@"日"
に書式設定したセル E1、F1 にそれぞれ「着工」、「完工」と入力しておき、
式 =IF($D3="社名1",INDEX($G$1:$AK$1,MATCH(E$1,$G3:$AK3,0)),"")
を入力したセル E3 を右隣および下方にオートフィル

【余談】素敵な添付図テクニックでした！

補足

ご回答ありがとうございます。ガンチャートの考え方で、日付から表示する方法ですね。試してみます。分かりやすくするために添付した画像でしたが、他の方からも丸投げだったので、今後も画像と自分の考えも含め質問させていただこうと思います。ありがとうございました。

回答No.5

noname#255669

セルの結合をという意見がありますが、結合はよほどのことがない限りしない方がいいです。あとから数式で参照したりするときに困ることがあります。
それに、結合するのにA3セル以降結合予定のセルに数式を入れても意味はありませんし(笑)

質問者さんの方法で見にくければA列を非表示にしておくといいですよね。
A列は所謂作業列だと思いますので。
数式は他の方の回答で出尽くしているので省きます。

お礼

ご回答ありがとうございます。A列は検索のために作成しました。私も結合はあまり好きではないのですが、チームリーダーの作成された表で、結合の解除ができませんでした。再度、チームリーダーに管理するには、この表はよろしくないことをお伝えしようと思います。ありがとうございました。

回答No.4

nishi6

5行１データで考えます。質問の例だと
　A2セル：=C2
　A3セル：=A2 、これをA6までコピー。
後は５セルごとにコピーします。
なぜ２つの式に分けたかというと、質問の添付図の場合、A2:A6を結合してしまえば見やすくなり、必要な算式は最初のA2セルだけですむからです。添付図上図。

　着工日　セルE3：=IFERROR(OFFSET($F$1,0,MATCH("着工",G3:AJ3,0)),"")
　完工日　セルF3：=IFERROR(OFFSET($F$1,0,MATCH("完工",G3:AJ3,0)),"")
「着工」や「完工」が列数で何番目にあるかMatch関数で調べ、F1セルからその列数番目を取り出しています。無ければ何も表示しません。５行ごとにコピーします。

差し出がましいですが、質問の添付図を見て、気になった点を記載させてもらいます。

Excelで表を作るとき、１データを複数行、複数列または複数行・複数列で作ることがあります。必然的な理由があるかもしれませんが、概して、必要となる算式は複雑になり、レイアウトの修正や算式の修正、データの消去などが問題になります。印刷時、頁数が増えたり、データの途中で頁が変わったりします。大変だったのは、１データの行数・列数が不定のケースでした。

１データに複数行・列 を使っていると、表全体の俯瞰性も落ちてきます。質問の添付図の場合、１データの日付部分の４行は未入力になるのでしょうか。
添付図の印刷物が必要な場合、かなりの頁数になりそうです。１データを複数行で作って、最後に集計や印刷で困って相談を受けた例はたくさんあります。

また、データベースを設計する場合、１データ１行とし、列方向には同じ種類のデータを登録するようにします。質問の添付図のC、D列は同じ列に異なる属性の項目が入力されており、算式を作ろうとすると複雑になりがちです。

また、C、D列とG列以降のデータ内容の属性は違っているように思えます。C、D列の内容については、別シートに「会社データ」のような表を作成し、その表に対しVlookup関数を使うといいでしょう。会社データの変更にも強くなります．質問の、A列の表示にVlookup関数を使うのは複雑になるでしょう。自分で自分を参照しているようにも見えます。

日付は列方向に進んでいますが、「着工日」が当月前だったり、「完工日」が翌月すぐだったりすると、「着工日」や「完工日」の表示が悩ましくなります。実際、データを発表するのは翌月になるため、そのような事例に対応するため、１年超の日付を作って登録し、当月着工、当月完工、未着工や未完工など、必要な案件を表示し、不必要な案件は非表示にしていました。年度をまたぐ未完工、未着手案件は、初期データとして事前入力していました。質問の添付図も１ヶ月だけの計算では正しく計算したものの、「着工日」が無かったりしかねません。

縦１列にデータを作成する例を添付します。添付図下図。
当方、Win10、Excel2010です。質問とは主旨が違うかもしれませんがご参考に。

お礼

私もデータベース化にしたかったのですが、チームリーダーからベースを変えずに、急ぎで対応が必要ということだったため暫定対応として質問させていただきました。現在、データベース化に向けて進めております。長文に渡ってアドバイス助かりました。ありがとうございました。

回答No.3

imogasi

既に回答は出ていますが、質問添付画像を添付して、丸投げ質問をするのも必要ですが、実現したいことを、文章に表現してみることが、考えるうえで、役立つと思う。解決に必要な点を自分でブレークダウンして、考え、判らない時にその必要点のみを質問する。
下記は既回答の解説版のようになりますが、参考にしてください。
例えば
E列については、文章で書いてみると、
（１）関数を入れた行で、G列からAJ列までで、「着工」という文字列がある列を探し、在れば、その列の第1行目のデータ（＝日付）を持ってくる。
AJ列は３１日分の日付列の終り。
F列については（（１）と同様ですが）
（２）関数を入れた行で、G列からAJ列までで、「完工」という文字列がある列を探し、在れば、その列の第1行目のデータ（＝日付）を持ってくる。
それで
（A)文字列のあるセルの列を探すのは、エクセル関数では、MATCH関数
（B)行は第I行目と決まっているが、指定列のデータを採ってくるのは　INDEX関数
これは検索関数に分類される関数などを、本やWEBで調べるか、経験で覚えておくしかない。
もう一つの問題は、３行目以下に同じ式を複写したいが、着工、完工のある以外の列は、空白にしたいわけだが、これが一苦労。
２００７以後は、「着工」などが見つからない場合に、空白を返す、少し式が短くなるIFERROR関数があるのでそれを使う。ISERROR関数に比べて、です。
データ例　質問添付画像のまま
E列　　F列　G列以右データ
　　　　　 2019/11/2 2019/11/3 2019/11/4 2019/11/5 2019/11/6　・・

11月2日 11月4日 着工 完工

11月4日 11月6日 着工 完工
E3の式は　=IFERROR(INDEX($G$1:$AJ$1,1,MATCH("着工",G3:AJ3,0)),"")
下方向に式を複写
F3の式は　=IFERROR(INDEX($G$1:$AJ$1,1,MATCH("完工",G3:AJ3,0)),"")
下方向に式を複写

お礼

適切なアドバイスありがとうございます。自分なりに関数を駆使しましたが、不明だっため画像での質問をしてしまいました。今後気をつけます。

回答No.2

aokii

A2に以下の式を入れて下へドラッグコピー
=IF(D2="",C2,A1)
E2に以下の式を入れて下へドラッグコピー
=IF(ISNA(OFFSET($F$1,0,MATCH("着工",$G2:$XF2,0))),"",OFFSET($F$1,0,MATCH("着工",$G2:$XF2,0)))
F2に以下の式を入れて下へドラッグコピー
=IF(ISNA(OFFSET($F$1,0,MATCH("完工",$G2:$XF2,0))),"",OFFSET($F$1,0,MATCH("完工",$G2:$XF2,0)))

お礼

迅速な解答ありがとうございます。いろんな関数があり、勉強になりました。