いつもお世話になっています。
早速ですが、下記のようなことをしたいのですが、
関数でできるでしょうか?
│ A │ B
-----------------------
1│日本太郎 │ ばら
-----------------------
2│日本太郎 │ さば
-----------------------
3│山田花子 │ キリン
-----------------------
4│山田花子 │ 米
-----------------------
5│日本太郎 │ イス
-----------------------
6│山田花子 │ インク
-----------------------
↓
│ A │ B
-----------------------
│ │ ばら
1 │日本太郎│ さば
│ │ イス
-----------------------
│ │ キリン
2 │山田花子│ 米
│ │ インク
-----------------------
A列に人物名、B列に品物を入力します。
誰かが何かを入手した際、その順に入力していきますので、
同一の人物が複数の行に亘って入力された表(上の表)があります。
これを基に「誰が何を持っているか」をまとめる表(下の表)を作りたいのですが、
上の表を基に下の表が自動的に出来上がるような関数はあるでしょうか?
VLOOKUPを使っても上手く出来ず、しかし他に思い浮かびません。
何か良い知恵がございましたら、お教え下さい。
No.3ベストアンサー
- 回答日時:
関数でもできなくはないですが、
下記のように作業列を複数使ったかなり複雑な処理になります。
以下、質問文上の表がSheet1にあり、下の表をSheet2に作るとします。
Sheet1!C1セル:=COUNTIF($A$1:A1,A1)
Sheet1!D1セル:=A1&C1
Sheet1!E1セル:=IF(C1=1,"",OFFSET($E$1,MATCH(A1&(C1-1),$D$1:D1,0)-1,)&CHAR(10))&B1
Sheet1!F1セル:=COUNTIF($C$1:C1,1)
として、C,D,E,F列を適当な行までフィル。
必要に応じて列を非表示に。
Sheet2!A1セル:=IF(ISNA(MATCH(ROW(),Sheet1!$F$1:$F$1000,0)),"",OFFSET(Sheet1!$A$1,MATCH(ROW(),Sheet1!$F$1:$F$1000,0)-1,))
Sheet2!B1セル:=IF(A1="","",VLOOKUP(A1&COUNTIF(Sheet1!$A$1:$A$1000,A1),Sheet1!$D$1:$E$1000,2,0))
として、適当な行までフィル。
Sheet2!B列について、セルの書式設定>配置>折り返して全体を表示する にチェック
Excel2003で動作確認。
Sheet2の行の高さは元データが変更されても自動では変わらないので、
その都度調整するか、あらかじめ十分な高さに設定しておく必要があります。
ありがとうございます。
ご教示の関数で、ほぼ私の考えていたことができました。
そこで少し拡張しようと思い、Sheet1の人物名の前に列を挿入して法人名を付けた場合(人物名をB列にして代表者の列とする)で、法人別でSheet2をまとめようとすると、
「法人名は違うが代表者名が同じ」と言う項目は上位の代表者にまとめられ、下位の代表者名や品名は「N#A」となり、どうしても上手く出来ませんでした。
下位の代表者名にスペースを加えると上手く分類できましたが、人物名に手を加えないで出来る方法を模索中です。
しかし、それでも思っていたことがほぼできたので、非常に助かりました。
ありがとうございました。
No.4
- 回答日時:
#3です。
●法人名を加える件について。
新たに作業列を加えて「法人名」と「代表者名」を結合した「一意な名前」を生成し、
#3の処理を「人物名」ではなく「一意な名前」をキーにして行えば、
代表者名が重複した場合もカバーできます(動作確認済)
また、法人ごとに並べることも可能ですが、
関数によるソートは別個の大きなハナシになりますので割愛します。
●蛇足アドバイス
(#3の時点で既にそうですが)こういった複雑な処理になりますと、
作成した時点ではともかく、将来的に保守や引継ぎの面で問題が発生しそうな気がします。
一長一短ありますが、この際マクロ(VBA)による処理を検討された方が良いかもしれません。
また、この処理でネックになっているのは、「一つのセルに纏める」という要件です。
おそらく「見た目」あるいは「セルを結合する手間」の問題だろうと推察しますが、
この点については、ソート結果に対し「条件付書式」で枠線処理をするというアプローチもあります。
「枠線非表示」にするかあるいは対象範囲を白背景で塗り潰してから、
「上隣セルと同値なら白字」&「上隣セルと異値なら上辺罫」などとすれば、
「結合して上詰め」と同様の見た目で、かつ、孫引き・ソート・フィルタも可能なので何かと潰しが効きます。
(後になって「法人の区切は二重線に」とか「縦位置は中央揃えに」とか気軽に言われて泣くこともありますが…)
以上、ご参考まで。
お礼の連絡、大変遅くなってしまい申し訳ありませんでした。
今やっている作業を申し上げますと、
法人名と代表者名(個人の場合は営業者名と店舗名)等を入力したシート、その会社が所持している品物と特徴を入力したシート、品物の型番と製造元を入力したシート、更に帳簿のテンプレートをそれぞれ作成し、
法人(又は店舗)毎に、Vlookupを使って帳簿シートに入力して印刷する、と言うことをしています。
> …の問題だろうと推察しますが、…
上記作業で、品物は随時増えていき、それを入手時期に応じて入力しますので、同一法人(又は営業者)の行がどんどん増えていくのですが、
帳簿シートの品名欄は1つですので、セルを纏める作業をしています。
前回ご回答の関数、私の思っていたことがほぼ(100パーセント近く)できて非常に助かり、現在は大変重宝しています。
前回の解答で追加要望を書き込みましたが、しかし関数をご教示頂く前に比べたら些細なことと思うようになりました。
今は現状で続けたいと考えています。
本当にありがとうございました。
また、何かの折に質問させて頂くと思いますが、その時はよろしくお願いします。
No.2
- 回答日時:
以下の様な抽出表となりますがが次の方法は如何でしょうか。
(品物が重複しても抽出します事ご承知下さい。)
A B C D
1 日本太郎 ばら さば イス
2 山田花子 キリン 米 インク
(1)元表をシート1として、抽出表をシート2のB1セルに次の数式を設定し、縦横に必要分コピー
元表範囲をA1:B200としていますので調整して下さい。
=IF(COLUMN(A1)-1<COUNTIF(Sheet1!$A$1:$A$200,$A1),INDEX(Sheet1!$B$1:$B$200,SMALL(IF(Sheet1!$A$1:$A$200=$A1,ROW(Sheet1!$A$1:$A$200),9999),COLUMN(A1))),"")
(2)抽出表のA列は、元表のA列を選択→データ→フィルタオプションの設定で重複レコードを無視をチェック→OKでコピー&ペーストして下さい。
ご回答、ありがとうございます。
早速試してみましたが「N/A」が返されて上手く出来ませんでした。
ただ、#1の方にも返信したように、品名は1つのセルに纏めたいと考えています。
教えて頂いた関数についてはもう少し試行錯誤してみたいと思いますが、別案も待ってみたいと思います。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセル 関数 指定の繰り返しの回数 以降(以前)を削除するには、 2 2022/04/24 10:29
- Excel(エクセル) エクセルで休憩時間を引く時と、引かない時の数式 3 2022/11/05 11:48
- MySQL 【MySQL】本当に困っているので、助けてください。よろしくお願いします。 3 2023/06/03 14:24
- Visual Basic(VBA) エクセルについて教えてください。 3 2023/06/28 09:11
- X(旧Twitter) X(Twitter)で名前を例えば山本太郎→山田花子→山本太郎に変更しようとしたら、プロフィール変更 1 2023/08/20 22:00
- その他(データベース) 20万行あるデータを動かしたい 2 2023/06/13 15:21
- C言語・C++・C# c言語の問題です 2 2023/07/21 10:51
- バラエティ・お笑い カラオケバトルの次の出演者の中で歌唱力も魂のこもりようも表現力も抜群と思う人を上位から 1 2022/05/04 14:04
- 哲学 《太郎ハ花子ガ好きだ》構文から《象は鼻が長い / 僕はウナギだ / コンニャクは太らない》へ 1 2022/05/30 08:48
- 政治 世論調査、国民の6割が国葬反対とは? マスコミが、またデマを流したのか? 国葬反対デモ参加人数 主催 15 2022/09/27 21:08
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
数学 Tan(θ)-1/Cos(θ)について...
-
スマートな関数を教えて下さい。
-
Excel ウインドウ枠の固定をす...
-
EXACT関数とIF関数の組み合わせ...
-
4つのパターンを表示するEXACT...
-
関数を教えて下さい。
-
Excel:一部のフォントでセルの...
-
エクセルで指定した日付、店舗...
-
エクセルでセルに「氏名を入力...
-
Excel 2019 のピボットテーブル...
-
スプレッドシートの関数VLOOKUP...
-
エクセルで日付の入ったセルの...
-
Excelファイルの「数式」タブ→...
-
excelの不要な行の削除ができな...
-
Excelのif関数で文字が見えなく...
-
各ページの1番上の表示について
-
エクセル関数に詳しい方、教え...
-
INDIRECTを使わず excelで複数...
-
Excel 2019 は、SPILL機能があ...
-
UNIQUE関数が使えないバージョ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ファイル内にある数字の出現回...
-
Excel関数の先頭に「@」が入っ...
-
エクセルの気味悪い不思議
-
Excel VBAで、実行時にsheet上...
-
表示されている人数だけを数え...
-
他人が作ったマクロの理解
-
Excelの関数について質問です。
-
Excel 集計表
-
エクセル 日時の計算式について
-
Excelの関数に関して質問です。...
-
エクセル:セル内の文字列の下...
-
絞り込み検索
-
エクセルの関数で
-
エクセルの書式設定について教...
-
余分なEXCELファイルに印刷され...
-
VBA 同一シート内での転記の仕方
-
長期休みの関数はありますか
-
Excelの空のセル
-
エクセルで入力してある文を別...
-
Excelのマクロで、セルを結合し...
おすすめ情報