皆様方の知恵をお借りに参りました。
あれこれ、サイト巡りして問題解決にあたりましたが、途方にくれています。
私のスキルは、入門者レベルで、至らない点は多くありますが、どうかお許しください。
まず、表をご覧ください。
D列からG列までの間に、データ入力をしております。実際は、200行分前後のデータあります。
D列、G列、K列、M列、O列、Q列は、「ユーザ定義」の設定において、「数値」にしてあります。
日付は、ランダムに入力しています。
これを、表のように、「車両費」など項目別に「日付」を検索して昇順させ、並べて
表示させたいのです。尚、中には、「車両費」の内訳が「空白欄」になっているものもあります。
さらに、「車両費」の内訳(ガソリン代・高速代など ---F列)を「項目別」にして、同じように
「日付」を昇順させ、表のように、「日付」「金額」のみ並べて表示させたいのです。
(データ入力の列をベースとしてください。)
お調べしましたところ、「配列数式」と呼ばれるものが必要なのかもしれません。
また関数は、INDEX関数、MATCH関数、SMALL関数などの組み合わせになるかしれません。
どなたか、表のように、表示させることのできる「関数」を教えてください。
No.1
- 回答日時:
これはどうしても関数で処理しなければいけないのですか?
例えば:
フィルタ処理で抽出すれば関数を使わずに処理もできる
仮に:
【D~G列に入力したものを即時反映させる必要が有るため、計算式を利用して実現したい】
等の場合
配列計算式を多用した場合、データが増えると処理が重くなり作業効率が悪くなる可能性があります
(各セル入力後、最計算待ちで入力できない)
ありがとうごさいます。
>これはどうしても関数で処理しなければいけないのですか?
はい、項目が多いため関数が必要と思っています。
>配列計算式を多用した場合、データが増えると処理が重くなり作業効率が悪くなる可能性
これに関しては、実は心配している事でした。
本当に困まりました。
No.3
- 回答日時:
No1です
では計算式で
K11セル
=IF(COUNTIF($E:$E,$L$10)>ROW()-11,OFFSET($A$1,SMALL(IF($E$11:$E$29=$L$10,ROW($E$11:$E$29),""),ROW(A1))-1,COLUMN(C:C)),"")
L11セル
=IF(COUNTIF($E:$E,$L$10)>ROW()-11,OFFSET($A$1,SMALL(IF($E$11:$E$29=$L$10,ROW($E$11:$E$29),""),ROW(B1))-1,COLUMN(E:E)),"")
M11セル
=IF(COUNTIF($E:$E,$L$10)>ROW()-11,OFFSET($A$1,SMALL(IF($E$11:$E$29=$L$10,ROW($E$11:$E$29),""),ROW(C1))-1,COLUMN(F:F)),"")
O11セル
=IF(COUNTIF($F:$F,$P$10)>ROW()-11,OFFSET($A$1,SMALL(IF($F:$F=$P$10,ROW($F:$F),""),ROW(D1))-1,COLUMN(C:C)),"")
Q11セル
=IF(COUNTIF($F:$F,$P$10)>ROW()-11,OFFSET($A$1,SMALL(IF($F:$F=$P$10,ROW($F:$F),""),ROW(G1))-1,COLUMN(F:F)),"")
微妙に計算式が違うので注意
すべて配列計算になるので、セル貼付け後に【Shift】+【Ctrl】+【Enter】で確定
各セルに貼り付けたらドラッグで下方向コピー
データがどこまで入力されるのか不明なので、計算範囲を列全体を指定しているので、とんでもなく再計算に時間がかかります
心より感謝申し上げます。
>データがどこまで入力されるのか不明なので、計算範囲を列全体を指定している
データ入力を200行までと限定した場合、
数式のどの部分を変更したら、よろしいのでしょうか?
このような幼稚な質問をして、まことに申し訳ございません。
No.4ベストアンサー
- 回答日時:
>データ入力を200行までと限定した場合、
>数式のどの部分を変更したら、よろしいのでしょうか?
列指定されている箇所
例えば
K11セルの場合だと
=IF(COUNTIF($E:$E,$L$10)>ROW()-11,OFFSET($A$1,SMALL(IF($E$11:$E$29=$L$10,ROW($E$11:$E$29),""),ROW(A1))-1,COLUMN(C:C)),"")
=IF(COUNTIF($E:$E,$L$10)>ROW()-11
この部分の
$E:$E
を
$E$11:$E$211 (11行目から211行目)
後半部分
OFFSET($A$1,SMALL(IF($E$11:$E$29=$L$10,ROW($E$11:$E$29)
$E$11:$E$29 (これは私のミスで本来は$E:$Eにする必要があった)
こちらも同様に
$E$11:$E$211
に変更、その他の計算式も変更箇所は同じです
web2525様、まことにありがとうございました。
とても助かりました。
後程、負荷軽減のため項目数を減らしたり、金額のみ表示させたり工夫してみるつもりです。
web2525様には心からのお礼を申し上げます。
No.5
- 回答日時:
race77さん、こんばんわ
マクロで実行するのが楽ではありますが、どうしても数式で実現したい場合は作業列を利用して実現できなくはありません。
例の通り、11行目からデータが始まっているものとします。(別に11行目から始まっている必要はありませんが、1行目から実データが始まっていると多少厄介ではあります)
また、これも例の通り、L10セルとP10セルに、車両費だのガソリン代だのを入力するものとします。
(ここを変更すると結果が変わる)
S列からAA列までを作業列として利用します(この列には他にデータを入れないで下さい。また、この列が既に埋まっていて利用できない場合は、以下の数式を右にずらして利用してください)
実際のデータは200行前後とありますが、1000行目までデータが入っても良いように作ってあります。これ以上データが増える場合は、数式を読み込めば、増やす方法も分かるはずです。
K11セル(大費目の抽出対象の日付)
=IF(ISNA(X11),"",INDIRECT("T"&X11))
(K列全体に対して、D列の書式をコピーすること)
L11セル(大費目の抽出対象の小費目)
=IF(ISNA(X11),"",INDIRECT("U"&X11))
(L列全体に対し、条件付書式で、「セルの値が 次の値に等しい 0」の時、フォント色を白色」に設定
M11セル(大費目の抽出対象の金額)
=IF(ISNA(X11),"",INDIRECT("V"&X11))
(M列全体に対し、G列の書式をコピーすること)
O11セル(小費目の抽出対象の日付)
=IF(ISNA(AA11),"",INDIRECT("K"&AA11))
(O列全体に対して、D列の書式をコピーすること)
Q11セル(小費目の抽出対象の金額)
=IF(ISNA(AA11),"",INDIRECT("M"&AA11))
(Q列全体に対し、G列の書式をコピーすること)
S11セル(L10セル(車両費)に該当する元の表の行番号の取得)
=MATCH($L$10,INDIRECT("E"&(S10)+1):$E$1000,0)+S10
(行を増やしたい場合は、ここの$E$1000の"1000"を増やす)
T11セル(S列で求めた行番号に該当する日付の取得)
=IF(ISNA(S11),"",INDIRECT("D"&S11))
U11セル(S列で求めた行番号に該当する大費目の取得)
=INDIRECT("F"&S11)
V11セル(S列で求めた行番号に該当する金額の取得)
=INDIRECT("G"&S11)
TUV列は、DFG列から「車両費」に相当する項目を抜粋した表となります。
これをベースに、以下並び替えを行います。
W11セル(T列(日付)をRANK関数にて昇順に順位をつける)
=IF(T11="","",RANK(T11,T:T,1))
X10セルとY10セルには、「1」を入れてください。これを行わないとX列が上手く計算できません。
X11セル(T列(日付)を、値の小さい順に並べたときの行番号)
=IF(ISNA(MATCH(Y10,INDIRECT("W"&X10+1):$W$1000,0)+X10),MATCH(Z11,W:W,0),MATCH(Y10,INDIRECT("W"&X10+1):$W$1000,0)+X10)
(行を増やしたい場合は、1000 (2箇所あります) の値を増やす)
Y11セル(同じ日付で同じ項目が存在した場合を考慮した、同率○位 の表示 X列の計算に利用)
=INDIRECT("W"&X11)
Z11セル(同率を考慮しない場合の順位 X列の計算に利用)
=Z10+1
AA列は、OQ列を求めるための作業列となります。
AA11セル(P10セル(ガソリン代)に該当するKLMの表の行番号の取得
=MATCH($P$10,INDIRECT("L"&(AA10)+1):$L$1000,0)+AA10
OQ列の表は、KLMの表を参照しているため、既に並び替えができているので、これだけでOKです。
ここまで入力したら、まずはS11セルをクリックした後、S11セルの右下をつまみ、1000行目までドラッグします。T11セルについては、T11セルを選択した後、T11セル右下へカーソルを持って行き、カーソルの形状が変わったところでダブルクリック、U11以降も同様にします。
作業列の表示が邪魔であれば、S列からAA列までを非表示にするなり、フォントを背景と同じ色にすると良いでしょう。
>実際のデータは200行前後とありますが、1000行目までデータが入っても良いように作っ
>てあります。
ありがとうございます。将来は、データ入力200行は突破しますと思いますので
配列数式より作業列を利用した方がベターなのかもしれません。
matsu_jun様、感謝いたします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) ExcelのIF関数について 4 2023/05/24 12:54
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Excel(エクセル) Excelのテーブルについて 6 2023/07/07 08:37
- Excel(エクセル) 列の複数ある空白セルを飛ばして、セルに並べて表示したい 3 2023/02/12 16:49
- C言語・C++・C# C言語初心者 ポインタについて、お助けください、、 2 2023/03/15 23:50
- その他(Microsoft Office) Excel2019と365、2021 2 2023/07/08 06:22
- Excel(エクセル) 名前と日付が一致する箇所にフラグを立てる関数が知りたいです 4 2022/08/11 02:24
- Excel(エクセル) Excel教えてください。 下記のことが出来るのは、マクロですか?条件付き書式でしょうか、、?知恵を 5 2022/11/12 09:33
- Excel(エクセル) 【Excel質問】 「本日の日付」から指定条件を満たす営業日経過後の日数を表示させる関数式 3 2022/06/06 23:28
- Visual Basic(VBA) エクセルVBAについて 2 2023/01/31 16:21
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル 文字を増やしたい。
-
エクセルの計算
-
セルの内容表示が邪魔になる
-
Excel
-
Microsoft365に変えたのですが...
-
エクセル:一覧表に存在する文...
-
エクセルで日付を数字+アルフ...
-
エクセルでの作業計算方法について
-
エクセルで年休を管理する方法...
-
はがきについて。
-
【マクロ】その時、その時で変...
-
excelの不要な行の削除ができな...
-
Microsoft1Officeの互換ソフト...
-
エクセル関数を教えてください
-
Excel ピボットテーブルで日付...
-
【マクロ】読取専用のファイル...
-
【関数】適切な文字数の数字を...
-
時間によってファイル名が変わ...
-
ある列、或いは、ある行のセル...
-
UNIQUE関数が使えないバージョ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報