【エクセル】 以前入力した行の内容を、別の行に表示

質問

エクセルの質問です。
分かる方がいらっしゃいましたら、教えてください。

エクセルで経理の表を作っています。
以下がその表です。

行
1　　　　支払日　　　支払先　　　支払内容　　　支払額
2　　　　　7/1　　　　　A会社　　　ケーブル　　　　5,000円　
3　　　　　7/5　　　　　B会社 　　　コピー機　　　　1,000円
4　　　　7/10　　 　C会社　　　　文具　　　　　　1,000円
5　　　　7/30 　 　 従業員Ａ　　交通費　　　　　3,000円
6 　　　合計　　　　　　10,000円
7
8
9　　　　　8/1　　　　　　C会社　　文具　　　　　　　2,000円
10　　　　8/10　　　　　 A会社　　ケーブル　　　　　5,000円

質問：
プルダウン表示で支払い先を選択すると、直近で入力した同じ支払先の行が表示される
方法はありませんでしょうか。

現在は、A会社の情報を入力するために、先月からA会社を探し、コピペしています。。

どうぞ宜しくお願いいたします。

回答No.8

MackyNo1

>上記の式をたとえば（Ｃ列）の「支払内容」の最新行に入れてみましたが、日付のシリアル値が表示されるのみでした。。

補足回答に気が付かず返信が遅くなり申し訳ありません。

ご質問はリストの一番下のデータの行を表示したいということでしたので、元データのリストの表（Sheet1)と検索する支払先のリストは別シートという前提で回答しています。

シリアル値で表示されるのは、セルの書式が「標準」になっているだけですので、ご希望の日付の表示形式してください。

もちろん行全体を表示したいということですから、補足情報のような設定ならA列にその数式を入力し、この数式をD列から右にコピー貼り付けしてください。

回答No.7

MackyNo1

>（元データがSheet1でB2セルに取引先ドロップダウンリストがある場合）。

=INDEX(Sheet1!A:A,MAX((Sheet1!$B$2:$B$1000=$B2)*ROW($B$2:$B$1000)))

とのことですが、上の数式はどこのセルに入れるのがよいのでしょうか。

元データがSheet1で、表示シートのB2セルに取引先ドロップダウンリストがある（取引先が入力されている）なら、どのセルに入力する場合も上記の式で最終行のデータが表示できます。

補足

元データはSheet1にあります。
Ｂ列が取引先を入力している列で、その列の新しい行をクリックするとドロップダウンリストが出るように設定しています。
（設定できるようにしました。ありがとうございました。）

上記の式をたとえば（Ｃ列）の「支払内容」の最新行に入れてみましたが、
日付のシリアル値が表示されるのみでした。。

たぶん、私の理解が足りないのだと思います。

重ねて大変申し訳ないのですが、なにが足りないのか教えていただけませんでしょうか。

宜しくお願いいたします。

回答No.6

某HN クロメート(Chromate)（@CoalTar）

C9セルにおいて
データ - 入力規則
入力値の種類:リスト
元の値:
=INDEX(C:C,MAX(IF(B$2:B8=B9,ROW(B$2:B8))))
エラーメッセージ スタイル:情報 などの「停止」以外
書式を下へコピー&ペースト

B9セルを入力して
[Tab]C列のセルに移り
[Alt]+[↓] ドロップダウンリストの表示
[↓]選択
[Enter]確定

補足

ありがとうございました。

図の解説もあり、大変分かりやすかったです。
しかし、該当する最終行すべてを表示させたいのです。
直近で支払った、その会社のその支払内容にかかる料金のデータがほしいのです。

直近の「行ごと」表示できる機能があれば教えてください。

回答No.5

MackyNo1

以下のような手順でにゅるよく規則のリストと配列数式を使えばご希望のドロップダウンリストからデータベースの最終行をひょぷじすることができます。

まずドロップダウンリストのデータを作成するために、たとえばデータベースシートのL1セル（どのシートでもよい）に以下の式を入力してCtrl＋Shift+Enterで確定して、下方向にオートフィルコピーして重複のない支払先リストを作成します（最終的にはこの列を非表示にする）。

=INDEX(Sheet1!B:B,SMALL(IF((MATCH(Sheet1!$B$2:$B$1000&"",Sheet1!$B$2:$B$1000&"",0)=ROW($B$2:$B$1000)-1)*(Sheet1!$B$2:$B$1000<>""),ROW($B$2:$B$1000),1001),ROW(A1)))

なお、配列数式を多用するとシートの動きが重くなるので、取引先＋アルファの数（将来の取引先の増加分）だけオートフィルコピーするようにしてください。

次に名前定義で取引先リストの範囲を以下のように指定します。
「数式」「名前の定義」で名前の欄にたとえば「取引先」と名前を付けて、参照範囲に以下の式を入力します。

=OFFSET($L$1,0,0,COUNTA($L:$L)-COUNT($L:$L),1)

最後にドロップダウンリストを作成したいセルを選択して、「データ」タブの「データの入力規則」から入力値の種類で「リスト」を選択し、元の値の欄に「=取引先」と入力します。

数式で上記のドロップダウンリストから選択した取引先の最終行データは以下のような配列数式で表示できます（元データがSheet1でB2セルに取引先ドロップダウンリストがある場合）。

=INDEX(Sheet1!A:A,MAX((Sheet1!$B$2:$B$1000=$B2)*ROW($B$2:$B$1000)))

上記の数式も配列数式ですので入力後Ctrl＋Shift+Enterで確定して右方向にオートフィルコピーしてください。

補足

ご回答ありがとうございました。

以下について質問させてください。

数式で上記のドロップダウンリストから選択した取引先の最終行データは以下のような配列数式で表示できます（元データがSheet1でB2セルに取引先ドロップダウンリストがある場合）。

=INDEX(Sheet1!A:A,MAX((Sheet1!$B$2:$B$1000=$B2)*ROW($B$2:$B$1000)))

とのことですが、上の数式はどこのセルに入れるのがよいのでしょうか。
理解が足りなくて申し訳ないのですが、詳細を教えていただけると助かります。

回答No.4

KURUMITO

回答No3です。
プルダウン表示の意味を勘違いしていたようですね。
フィルタ操作で例えばA社の最新の行を表示させたいのでしたら回答3で示したようにシート1のE列に作業列を設け、E列を重点にフィルタ操作をすればよいでしょう。

回答No.3

KURUMITO

A会社の情報を入力するために、先月からA会社を探し、コピペしています。とのことですからシート1にお示しのようなデータが有るとしてシート2に直近で入力した同じ支払先の行が表示されればよいのでしょう。
次のようにすればよいでしょう。
シート1には作業列を設けE2セルには次の式を入力して下方にドラッグコピーします。

=IF(ISNUMBER(A2),IF(COUNTIF(B$2:B2,B2)=COUNTIF(B:B,B2),B2,""),"")

シート2のA2セルから下方にはプルダウンで会社名を表示できるとします。
B1セルから横の列にはシート1での項目名を並べます。
B2セルには次の式を入力して横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF(OR($A2="",COLUMN(A1)>COUNTA(Sheet1!$1:$1)),"",INDEX(Sheet1!$A:$XX,MATCH($A2,Sheet1!$E:$E,0),COLUMN(A1)))

B列には日付がシリアル値で表示されますのでB列を選択して右クリックし「セルの書式設定」の「表示形式」で日付から表示を選択します。
なお、この作業ではシート1の下行ほど最新のデータが入力された行としています。
A2セルのプルダウンで会社名を選択したい会社名にすればよいのですが、その下行にも別の会社の最新データを表示させることができますね。

補足

ご回答ありがとうございました。
初歩の初歩でもうしわけないのですが、

＞シート2のA2セルから下方にはプルダウンで会社名を表示できるとします。
はどのようにすると、プルダウンで表示できるようになるのでしょうか。

＞B1セルから横の列にはシート1での項目名を並べます。
の「B1セルから横の列」とは、どういう意味でしょうか。
B1セルの横の列はA列かC列だと思いますが、すみません、理解できていないので、もう少し詳しく教えてください。

宜しくお願いします。

回答No.2

bajutsu

オートフィルタ

補足

ご回答ありがとうございました。

オートフィルタは現在も利用していますが、
Ａ会社でフィルタをかけても、支払内容がいくつか種類があり、どれを選択すればよいか、いまいち判別がつかない状態です。

また、なにかよい案がありましたら、ぜひ教えてください。

回答No.1

keithin

そういった使い方では、言わずもがなですが支払内容列に「関数を入れておいて表示させる」みたいな使い方は出来ません。

＃「Ａ社＝ケーブル」だけしかない、といった事なら話も違ってきますが。そういう事はふつーにありえないとして。

一般に推奨できる方法：
Ａ社の代表的な支払内容を、あなたの知識によってリストアップします。たとえば「ケーブル」「プラグ」「コード」
Ｂ社の代表的な支払内容も、同様にリストアップします。たとえば「コピー機」「ファックス」「シュレッダ」「パソコン」

下記を参考に、「Ａ社」や「Ｂ社」を記入したら該当する支払内容が「入力規則」によって選択できるように仕込みます。
http://www.newcom07.jp/EXCEL-database/blog/excel_coffee_break/2011/03/post-117.html

ここで追加として、入力規則の「エラーメッセージ」のタブで「無効なデータが入力されたらエラーメッセージを表示する」のチェックを外しておきます。こうしておかないと、各社でリストアップしておいた代表的な取引内容「以外の内容」を記入できなくなります。

知識があればできる方法：
マクロを駆使して、各社を記入したら自動でマクロに割り込ませて、「至近の同じ会社の取引内容」をコピーして自動記入させる事も、やれば出来ます。
オモチャとしては面白いですが、ふつーこんな事にマクロをわざわざ組み込んで使ったりはしませんが。

作成例：
Ｂ列に支払先
Ｃ列に支払内容

シート名タブを右クリックしてコードの表示を選ぶ
現れたシートに下記をコピー貼り付ける

private sub worksheet_change(byval Target as excel.range)
　dim h as range, hx as range
　set target = application.intersect(target, range("B:B"))
　on error resume next
　for each h in target
　if h <> "" then
　set hx = range("B:B").find(what:=h.value, after:=h, lookin:=xlvalues, lookat:=xlwhole, searchdirection:=xlprevious)
　h.offset(0, 1) = hx.offset(0, 1)
　end if
　next
end sub

ファイルメニューから終了してエクセルに戻る
Ｂ列に会社を記入、Ｃ列に内容を記入していく。

補足

ご回答ありがとうございました。

お察しのとおり、A会社＝ケーブルだけではなく、支払内容として別のサービスもあります。
入力規則で設定すれば、直近の該当行を拾ってきてくれるのでしょうか？
ご教示いただきましたアドレスを開くことができず、私のエクセル知識が乏しすぎて、先に進むことができないのですが、有効なアドレスを教えていただけると助かります。

マクロは私には難しすぎて、とても手をだせませんが、エクセルの知識が増えてきましたら、参考にさせていただきたいと思います。

本当にありがとうございました。