関数の設定方法

質問

初投稿させていただきます。

ＥＸＣＥＬの関数についてのご質問です。

会社で支払業務を担当しているのですが、下記の場合どのように関数を設定すればよろしいでしょうか？

(1)当社銀行の件数を知りたい。（この場合だと三菱3件、みずほ１件、住友１件）
(2)　(1)の条件を踏まえてコードが重複しているものは２件でなく１件扱いしたい。
（※コード１山田とコード１山田２は同じ支払先）　（この場合だと三菱2件、みずほ１件、住友１件）

コード名前 相手銀行　　当社銀行 金額
1　　山田 　　　　りそな 　三菱 　　　　　50
2　　佐藤 　　　　みずほ 　みずほ　　　　100
3　　田中 　　　　住友 　住友 　　　　　200
1　　山田２　　　りそな 　三菱 　　　　　300
4　　鈴木 　　　　りそな 　三菱 　　　　　250
(1)の条件だけだったらＣＯＵＮＴＩＦを使用すればいいことまでは分かったのですが、
(2)の条件が加わると関数は何を追加すればよろしいでしょうか？

どなたかご存知の方がいらっしゃいましたら教えてください。

回答No.2

tamiemon96 · ベストアンサー

私なら、ですが・・・

１）　表全体をソートする

　　　当社銀行　支払先コード　の順で

　　＊これで、当社銀行別に、支払先企業が整列します。

２）件数カウント用の欄を一つ付け足します。

　　Ｆ列に「カウント」という作ったと仮定すると、

　　Ｆ２　に　　=if（A２＝A１,0,1）　という式を入力し、表の一番下までドラッグコピーします。

　　＊Ａ列（企業のコード）が1行上と一緒なら「０」を、違えば「１」を表示しなさい　という式です。
　　　これで、企業別に整列した表で、最初に出てきたときだけ「１」が表示されるので、「１」の数が
　　　企業数と一緒になりますね。

３）　あとは、このまま、エクセルの集計機能を使えば、企業の件数も企業ごとの支払金額も一緒に表示されます。

もう一つ裏技（？）

　　この表を元に「ピポットテーブル」を作っちゃえばいいと思います。

　　　縦軸に　銀行名、企業名　を　使えば　ＯＫでしょう。
　　　出来上がったピポットテーブルの表の欄外で、1件目に１　2件目に２といれて、あとはドラッグしていけば・・・・

　　　関数でなくても、必要なデータが取れると思いますよ。

お礼

tamiemon96さん。　ご回答ありがとうございました。
実は、後輩社員も、支払業務をやっているので、その子の為にも・・・
っていうのでこ今回、ご質問したんですよ。私も知らなかった事なんで覚えておきたかったというの勿論あったんですが・・・・
両方ともやってみて私はできたんですが、後輩社員に教えたところ、
「エクセル詳しくないんで、私には無理かも．．．」という控えめな返事がかえってきちゃいました。(´□｀)　
それでも私がこれを体得できたので満足です。後輩も必要になったときがくれば
また、教えればいいかなとは思います。

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

回答No.1

KURUMITO

当社銀行の名前までも自動的に表示させる表を作るためには、また,計算に負担となる配列数式などを使わないで簡単な式を使って対応するためにはいくつかの作業列を用意します。
元の表がシート1に有り、A1セルにコード、B1セルに名前、C1セルに相手銀行、D1セルに当社銀行、E1セルに金額の文字列がそれぞれ入力されており、それぞれのデータが下行にあるとします。
F2セルには次の式を入力して下行にオートフィルドラッグします。

=IF(D2="","",IF(COUNTIF(D$2:D2,D2)=1,ROW(D2)*1000+COUNTIF(D:D,D2),""))

G2セルには次の式を入力して下行にオートフィルドラッグします。

=A2&D2

H2セルには次の式を入力して下行にオートフィルドラッグします。

=IF(G2="","",IF(COUNTIF(G$2:G2,G2)=1,D2,""))

I2セルには次の式を入力して下行にオートフィルドラッグします。

=IF(H2="","",IF(COUNTIF(H$2:H2,H2)=1,ROW(H2)*1000+COUNTIF(H:H,H2),""))

これらの作業列が目障りでしたら列を選択して右クリックし、「非表示」を選択すれれば良いでしょう。

お求めの表ですが、別のシートに表示させるとしてA1セルには当社銀行、B1セルには件数、C1セルにはコード別件数とでも入力します。特にA1セルには必ず文字列を入力してください。
そこでA2セルには次の式を入力して右横方向にオートフィルドラッグしたのちに下方にもオートフィルドラッグします。

=IF(COUNTA(A$1:A1)>COUNT(Sheet1!$F:$F),"",IF(COLUMN(A1)=1,INDEX(Sheet1!$D:$D,MATCH(SMALL(Sheet1!$F:$F,ROW(A1)),Sheet1!$F:$F,0)),IF(COLUMN(A1)=2,MOD(SMALL(Sheet1!$F:$F,ROW(A1)),1000),IF(COLUMN(A1)=3,MOD(SMALL(Sheet1!$I:$I,ROW(A1)),1000),""))))

お礼

KURUMITOさん、　早速のご回答ありがとうございました。
と同時にお詫びしたいことが。　ちょっと私には難しくてできそうにもないかなっていうのが印象です。
私の欲になってしまうのですが、入れる計算式は少なめにしたいなあっていうのが今回ありました。
ただ、やらずじまいだと上記の後輩君と一緒になってしまうので必ず実践してみます。
今回はありがとうございました。