エクセルについて

質問

下記のような処理を関数でしたいです。
例えば下記のように入力されている表があるとします。
A1 1月1日 B1 100円 C1 空欄 D1 現金 E1 データＡ
A2 1月2日 B2 空欄 C2 101円 D2 現金 E2 データＢ
A3 1月3日 B3 101円 C3 空欄 D3 電子マネー E3 データＡ
A4 1月4日 B4 102円 C4 空欄 D4 現金 E4 データＣ

これを下記のように現金の部分だけ抽出しデータＡもしくはデータＢと入力されているものは金額と空欄の部分を逆にしたいです。

G1 1月1日 H1 空欄 I1 100円 J1 現金 K1 データＡ
G2 1月2日 H2 101円 I2 空欄 J2 現金 K2 データB
G3 1月4日 H3 102円 I3 空欄 J3 現金 K3 データC

回答No.8

MackyNo1

補助列などが必要のない数式で対応する場合は、以下のような配列数式を使うことになります。

=IF(COUNTIF($D$1:$D$100,"現金")<ROW(A1),"",INDEX($A:$D,SMALL(IF($D$1:$D$100="現金",ROW($A$1:$A$100),""),ROW(A1)),COLUMN(A1)))

ひとまず上記の数式を入力して、Ctrl+Shift+Enterで確定し、右方向に４つ下方向に適当数オートフィルすれば、現金の該当データが抽出されます（適宜セルの表示形式を日付などに変更する）。

この2列目の数式と3列目の数式には、表示列を入れ替えるため、以下のような調整を行います（COLUMN関数に(（$E$1:$E$100="データＡ")+($E$1:$E$100="データＢ")）を加減）。

2列目のセル
=IF(COUNTIF($D$1:$D$100,"現金")<ROW(A1),"",INDEX($A:$D,SMALL(IF($D$1:$D$100="現金",ROW($A$1:$A$100),""),ROW(A1)),COLUMN(A1)+(($E$1:$E$100="データＡ")+($E$1:$E$100="データＢ"))))

2列目のセル
=IF(COUNTIF($D$1:$D$100,"現金")<ROW(A1),"",INDEX($A:$D,SMALL(IF($D$1:$D$100="現金",ROW($A$1:$A$100),""),ROW(A1)),COLUMN(A1)-(($E$1:$E$100="データＡ")+($E$1:$E$100="データＢ"))))

回答No.7

imogasi

この質問はエクセルの「表の組み換え」の問題だ。
関数では、例えば現金を指定して現金を抜き出す関数は、複雑な式になる。普通ここへ質問するレベルの人では意味がわからないと思う。丸写しして、勉強して、応用するのも手である。
ーー
私はこういう「表の組み換え」にはエクセルVBAが必要と思っている。
判らないかもしれないが、書いておく。
例データ
Sheet1のA2:E5に（-は空白セルを示す）
1月1日 100円 - 現金 データＡ
1月2日 - 101円 現金 データＢ
1月3日 101円 - 電子マネー データＡ
1月4日 102円 - 現金 データＣ
こういう風に質問文に例データを書いてくれると判りやすい（希望）。一々セルごとにセル番地を書かれると視認性を損なう。
ーー
コード　ヒョウジュンモジュールの
Dim sh1, sh2
Set sh1 = Worksheets("Sheet1") 'Sheet1を使う。原データ
Set sh2 = Worksheets("Sheet2") 'Sheet1を使う。結果データ
k = 2 '結果データは２行目から
d = sh1.Range("A65536").End(xlUp).Row '原データ最終行
MsgBox d
For i = 2 To d
'--D列が現金の行を対象にするそれ以外は、書き出しはしない
If sh1.Cells(i, "D") = "現金" Then
sh2.Cells(k, "A") = sh1.Cells(i, "A")
sh2.Cells(k, "B") = sh1.Cells(i, "C")
sh2.Cells(k, "C") = sh1.Cells(i, "B")
sh2.Cells(k, "D") = sh1.Cells(i, "D")
sh2.Cells(k, "E") = sh1.Cells(i, "E")
k = k + 1 '次の書き出しは１行下への用意
End If
Next i
End Sub
このコード（のIFからENDIF間での部分以外は）は他の課題にも多くの場合で使える。少し慣れると直ぐ思いつく。
ーー
実行結果　（Sheet2
（Sheet2のA列は表示形式を日付に設定する）
1月1日 - 100円 現金 データＡ
1月2日 101円 - 現金 データＢ
1月4日 102円 現金 データＣ

回答No.6

noname#204879

別解です。（添付図参照）

Sheet2 において、
A2: =IF(Sheet1!A2="","",Sheet1!A2)
B2: =IF(OR($E2="データＡ",E2="データＢ"),IF(Sheet1!$C2,Sheet1!$C2,""),IF(Sheet1!$B2,Sheet1!$B2,""))
C2: =IF(OR($E2="データＡ",$E2="データＢ"),IF(Sheet1!$B2,Sheet1!$B2,""),IF(Sheet1!$C2,Sheet1!$C2,""))
D2: =IF($A2="","",Sheet1!D2)
セル D2 を右隣にドラッグ＆ペースト
範囲 A2:E2 を下方にズズーッとドラッグ＆ペースト
最後に、[オートフィルタ]で「種類」(Ｄ列)の“現金”を抽出すればＯＫ。

回答No.5

KURUMITO

F列を作業列としてF１セルには次の式を入力して下方にオートフィルドラッグします。

=IF(D1="現金",COUNTIF(D$1:D1,D1)-IF(OR(E1="データA",E1="データB"),0.5,0),"")

次にお求めの表ですがG１セルには次の式を入力してK１セルまで横にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(ROW(A1)>COUNT($F:$F),"",INDEX($A:$E,MATCH(ROW(A1),$F:$F,1),IF(COLUMN(A1)=1,1,IF(COLUMN(A1)=2,IF(MOD(INDEX($F:$F,MATCH(ROW(A1),$F:$F,1)),1)=0.5,3,2),IF(COLUMN(A1)=3,IF(MOD(INDEX($F:$F,MATCH(ROW(A1),$F:$F,1)),1)=0.5,2,3),COLUMN(A1))))))

最後にG列を選択して右クリックし、「セルの書式設定」から「表示形式」の「日付」で望みの日付を選択します。
また、H及びI列ではB及びC列で空白のセルには０が表示されますがそれらを見かけ上で空白にするためには、H及びI列を選択し「セルの書式設定」から「表示形式」の「ユーザー定義」で　0;; と入力すればよいでしょう。

回答No.4

okormazd

表は、「A2:E5」にあるとしています。A1からの表なら、「-ROW($A$1)」をすべて削除してください。

下記数式は、いずれも配列数式なので、数式入力後、数式バーにカーソルがある状態で、「Shift+Ctrl+Enter」を押す必要があります。数式の両端に「{ }」がついて、「{=INDEX(・・・・・)}」のように表示されます。
ほとんど同じ式で、最後の列インデックスの値が値がうだけです。
後、必要なだけ下にフィルしてください。

数式が長いですが、作業領域は必要ありません。

なお、「0」が表示されて面白くないというのであれば、「ツール」―「オプション」―「表示」―「ゼロ値」のチェックをはずせばいいでしょう。

G2:
=INDEX($A$2:$E$5,SMALL(IF($D$2:$D$21="現金",ROW($D$2:$D$21)-ROW($A$1),""),ROW()-ROW($A$1)),1)

H2:
=IF(OR(K2="データＡ",K2="データＢ"),INDEX($A$2:$E$5,SMALL(IF($D$2:$D$21="現金",ROW($D$2:$D$21)-ROW($A$1),""),ROW()-ROW($A$1)),3),INDEX($A$2:$E$5,SMALL(IF($D$2:$D$21="現金",ROW($D$2:$D$21)-ROW($A$1),""),ROW()-ROW($A$1)),2))

I2:
=IF(OR(K2="データＡ",K2="データＢ"),INDEX($A$2:$E$5,SMALL(IF($D$2:$D$21="現金",ROW($D$2:$D$21)-ROW($A$1),""),ROW()-ROW($A$1)),2),INDEX($A$2:$E$5,SMALL(IF($D$2:$D$21="現金",ROW($D$2:$D$21)-ROW($A$1),""),ROW()-ROW($A$1)),3))

J2:
=INDEX($A$2:$E$5,SMALL(IF($D$2:$D$21="現金",ROW($D$2:$D$21)-ROW($A$1),""),ROW()-ROW($A$1)),4)

K2:
=INDEX($A$2:$E$5,SMALL(IF($D$2:$D$21="現金",ROW($D$2:$D$21)-ROW($A$1),""),ROW()-ROW($A$1)),5)

回答No.3

kagakusuki

【方法その1】
　まず、M1セルに次の数式を入力して下さい。

=IF(INDEX($D:$D,ROW())="現金",ROW(),"")

　次に、M1セルをコピーしして、M2以下に貼り付けて下さい。

　次に、G2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNTIF($D:$D,"現金"),"",INDEX($A:$A,SMALL($M:$M,ROWS($2:2))))

　次に、H2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNTIF($D:$D,"現金"),"",IF(INDEX(IF(OR($K2="データＡ",$K2="データＢ"),$C:$C,$B:$B),SMALL($M:$M,ROWS($2:2)))="","",INDEX(IF(OR($K2="データＡ",$K2="データＢ"),$C:$C,$B:$B),SMALL($M:$M,ROWS($2:2)))))

　次に、I2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNTIF($D:$D,"現金"),"",IF(INDEX(IF(OR($K2="データＡ",$K2="データＢ"),$B:$B,$C:$C),SMALL($M:$M,ROWS($2:2)))="","",INDEX(IF(OR($K2="データＡ",$K2="データＢ"),$B:$B,$C:$C),SMALL($M:$M,ROWS($2:2)))))

　次に、J2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNTIF($D:$D,"現金"),"","現金")

　次に、K2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNTIF($D:$D,"現金"),"",INDEX($E:$E,SMALL($M:$M,ROWS($2:2))))

　次に、G2～K2の範囲をコピーしして、同じ列の2行目以下に貼り付けて下さい。

【方法その2】
　まず、G2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNTIF($D:$D,"現金"),"",INDEX($A:$A,SUMPRODUCT(ROW($D$1:INDEX($D:$D,MATCH("゛",$D:$D,-1)))*($D$1:INDEX($D:$D,MATCH("゛",$D:$D,-1))="現金")*(COUNTIF(OFFSET($D$1,,,ROW($D$1:INDEX($D:$D,MATCH("゛",$D:$D,-1)))-ROW($D$1)+1),"現金")=ROWS($2:2)))))

　次に、H2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNTIF($D:$D,"現金"),"",INDEX(IF(OR($K2="データＡ",$K2="データＢ"),$C:$C,$B:$B),SUMPRODUCT(ROW($D$1:INDEX($D:$D,MATCH("゛",$D:$D,-1)))*($D$1:INDEX($D:$D,MATCH("゛",$D:$D,-1))="現金")*(COUNTIF(OFFSET($D$1,,,ROW($D$1:INDEX($D:$D,MATCH("゛",$D:$D,-1)))-ROW($D$1)+1),"現金")=ROWS($2:2))))&"")

　次に、I2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNTIF($D:$D,"現金"),"",INDEX(IF(OR($K2="データＡ",$K2="データＢ"),$B:$B,$C:$C),SUMPRODUCT(ROW($D$1:INDEX($D:$D,MATCH("゛",$D:$D,-1)))*($D$1:INDEX($D:$D,MATCH("゛",$D:$D,-1))="現金")*(COUNTIF(OFFSET($D$1,,,ROW($D$1:INDEX($D:$D,MATCH("゛",$D:$D,-1)))-ROW($D$1)+1),"現金")=ROWS($2:2))))&"")

　次に、J2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNTIF($D:$D,"現金"),"","現金")

　次に、K2セルに次の数式を入力して下さい。

=IF(ROWS($2:2)>COUNTIF($D:$D,"現金"),"",INDEX($E:$E,SUMPRODUCT(ROW($D$1:INDEX($D:$D,MATCH("゛",$D:$D,-1)))*($D$1:INDEX($D:$D,MATCH("゛",$D:$D,-1))="現金")*(COUNTIF(OFFSET($D$1,,,ROW($D$1:INDEX($D:$D,MATCH("゛",$D:$D,-1)))-ROW($D$1)+1),"現金")=ROWS($2:2)))))

　次に、G2～K2の範囲をコピーしして、同じ列の2行目以下に貼り付けて下さい。

回答No.2

tom04

No.1です！
たびたびごめんなさい。

投稿した後で前回、余計なことを書いていたのに気づきました。
※以下の数行がが不必要でした。

前回の数式だと、列すべてを範囲指定していますので、
どの行からデータが始まっていてもちゃんと表示されると思います。

何度も失礼しました。m(_ _)m

回答No.1

tom04

こんばんは！
現金出納帳に転記するような感じですかね。

一例です。
↓の画像（小さくて見づらかったらごめんなさい。）でM列を作業用の列として使っています。

作業列M1セルに
=IF(D1="現金",ROW(),"")
という数式を入れ、オートフィルでずぃ～～～！っと下へコピーしておきます。
（作業列が目障りであれば遠く離れた列を利用するか、M列を「非表示」にします）

そして、G1セル（セルの表示形式は「日付」にしておく）に
=IF(COUNT(M:M)<ROW(A1),"",INDEX(A:A,SMALL(M:M,ROW(A1))))

H1セルに
=IF($G1="","",IF(INDEX($B:$C,SMALL($M:$M,ROW(A1)),COLUMN($C$1)-COLUMN(A1))="","",INDEX($B:$C,SMALL($M:$M,ROW(A1)),COLUMN($C$1)-COLUMN(A1))))
として隣のI1セルまでオートフィルでコピー！

J1セルに
=IF($G1="","",INDEX(D:D,SMALL($M:$M,ROW(A1))))
として隣のK1セルまでコピー！

最後にG1～K1セルを範囲指定　→　K1セルのフィルハンドルで下へコピー！

これで画像のような感じになります。

※　質問では1行目からのデータになっていますが、通常1行目は項目行になっていると思いますので、
数式のセル番地は実状に応じて適宜変更してみてください。

以上、参考になれば良いのですが・・・m(_ _)m