Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Home : Excel 2013)
必要なデータ(文字含む)のみ表示させたい
投稿日時: 21/11/17 12:28:54
投稿者: koji-t

YAHOO知恵袋にも質問していて重複しますがこちらでは画像添付できないみたいなのでよろしく教えてください。
 
https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q14252595899
 
画像のようなデータをつくり管理していますが個別に抽出して表示する方法がわかりません。
画像のABCDFはセルの結合をしていてEとGはセルの結合していません。
どのセルも計算式や関数式は入っていません。
BとDのセルは「データの入力規則」でドロップダウン選択にしています。
 
内訳と備考も連動して表示されるようにできないでしょうか?
 
 
1:検索抽出で(仕入れ業者)の〇社のデータだけを見たい。
2:検索抽出で(店舗名)の〇店のデータだけを見たい。
3:検索抽出で(分類)の〇〇のデータだけをみたい。
 
各々別々に閲覧するので仕入業者が〇社で店舗は〇店などの複合検索はしません。
 
※1店舗名と分類のセルはドロップダウンリスト式にしています。
※2内訳別や金額別、備考別などでの抽出検索はしません。

回答
投稿日時: 21/11/17 14:34:22
投稿者: hatena
投稿者のウェブサイトに移動

下記が参考になるかと思います。
 
Office TANAKA - Excel Tips[結合セルをオートフィルタで絞り込む]
http://officetanaka.net/excel/function/tips/tips64.htm

回答
投稿日時: 21/11/17 15:13:40
投稿者: んなっと

●例えば下のようにB列に結合セルがあって、B列でオートフィルター可能にしたいとします。
 
  B   C  D E
┌――┬――┐
| 市|分類|
├――┼――┤
|  | あ|
|  ├――┤
|横浜| い|
|  ├――┤
|  | う|
├――┼――┤
|  | あ|
|  ├――┤
|東京| い|
├――┼――┤
|  | あ|
|  ├――┤
|  | い|
|  ├――┤
|千葉| う|
|  ├――┤
|  | え|
└――┴――┘
こんな作業をすることになります。
[1] B4:B32をE列にコピー
 
  B   C  D E
┌――┬――┐    
| 市|分類|    
├――┼――┤┌――┐
|  | あ||  |
|  ├――┤|  |
|横浜| い||横浜|
|  ├――┤|  |
|  | う||  |
├――┼――┤├――┤
|  | あ||  |
|  ├――┤|  |
|東京| い||東京|
├――┼――┤├――┤
|  | あ||  |
|  ├――┤|  |
|  | い||  |
|  ├――┤|  |
|千葉| う||千葉|
|  ├――┤|  |
|  | え||  |
└――┴――┘└――┘
 
[2] B4:B32を結合解除
 
  B   C  D E
┌――┬――┐    
| 市|分類|    
├――┼――┤┌――┐
|横浜| あ||  |
├――┼――┤|  |
|  | い||横浜|
├――┼――┤|  |
|  | う||  |
├――┼――┤├――┤
|東京| あ||  |
├――┼――┤|  |
|  | い||東京|
├――┼――┤├――┤
|千葉| あ||  |
├――┼――┤|  |
|  | い||  |
├――┼――┤|  |
|  | う||千葉|
├――┼――┤|  |
|  | え||  |
└――┴――┘└――┘
 
[3] B4:B32を選択した状態で
 Ctrl+Gキーでジャンプ
→セル選択
→空白セル
→OK
→イコール(=)を入力して上矢印キー(↑)
→Ctrl+Enter
 
  B   C  D E
┌――┬――┐    
| 市|分類|    
├――┼――┤┌――┐
|横浜| あ||  |
├――┼――┤|  |
|横浜| い||横浜|
├――┼――┤|  |
|横浜| う||  |
├――┼――┤├――┤
|東京| あ||  |
├――┼――┤|  |
|東京| い||東京|
├――┼――┤├――┤
|千葉| あ||  |
├――┼――┤|  |
|千葉| い||  |
├――┼――┤|  |
|千葉| う||千葉|
├――┼――┤|  |
|千葉| え||  |
└――┴――┘└――┘
 
[4] E4:E32をB4:B32に書式貼り付け
 
  B   C  D E
┌――┬――┐    
| 市|分類|    
├――┼――┤┌――┐
|  | あ||  |
|  ├――┤|  |
|横浜| い||横浜|
|  ├――┤|  |
|  | う||  |
├――┼――┤├――┤
|  | あ||  |
|  ├――┤|  |
|東京| い||東京|
├――┼――┤├――┤
|  | あ||  |
|  ├――┤|  |
|  | い||  |
|  ├――┤|  |
|千葉| う||千葉|
|  ├――┤|  |
|  | え||  |
└――┴――┘└――┘
 
→E列削除
 
●面倒ですね。そこで...仮に一度だけVBAを使っていいなら...
 
結合セル範囲(上の例ではB4:B32)を選択した状態
 
Sub test()
  Dim Sht As Worksheet
  Dim tmpSht As Worksheet
  Dim Rng As Range
  Set Sht = ActiveSheet
  Set Rng = Selection
  Rng.Copy
  Set tmpSht = Sheets.Add(After:=Sheets(Sheets.Count))
  tmpSht.Paste
  With Rng
    .UnMerge
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
  End With
  tmpSht.Range("A1").CurrentRegion.Copy
  Rng.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
      SkipBlanks:=False, Transpose:=False
  Application.DisplayAlerts = False
  tmpSht.Delete
  Application.DisplayAlerts = True
  Sht.Select
End Sub
 
 

回答
投稿日時: 21/11/17 15:16:52
投稿者: んなっと

最初の方法の中の
 イコール(=)を入力して上矢印キー(↑)
→Ctrl+Enter
は覚えておくと便利なテクニックです。

回答
投稿日時: 21/11/17 15:43:32
投稿者: Suzu

・この表を使うのが自分以外も居る
・結合セル(仕入れ業者、店舗、分類)に入力されているデータの件数が変わる
のであれば、
別に作業列を持っておいて、その作業列では、連結セルの値を参照する様なフォーマットにしておき
その列でオートフィルターの抽出を行うようにしておいた方が他のユーザーとしては判り易いかと思います。

回答
投稿日時: 21/11/19 16:18:02
投稿者: んなっと

関数でやる場合の例。
 
I1に業者,店,分類からリスト選択
I2にA社,B社,渋谷店,品川店,果物,菓子などI1に応じて変化するリスト選択
 
J:M列,F列は元の表と同じセル結合
 
    A   B    C   D    E   F   G H    I   J   K
 1 業者 日付   店 分類  内訳 金額  備考    店▼ 業者 日付
 2  A社 10/2 渋谷店 果物 いちご  780     品川店▼  B社 10/2
 3              みかん                  
 4                                   
 5                                   
 6  B社 10/2 品川店 果物 いちご  240           B社 10/9
 7              みかん                  
 8                                   
 9                                   
10  A社 10/4 渋谷店 菓子 チョコ  700 20%引            
11              ポテチ                  
12                                   
13                                   
14  B社 10/9 品川店 果物 バナナ  300               
15              りんご                  
16              キウイ                  
17                                   
 
 
J2
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($2:$5000)/(INDEX($A$2:$D$5000,,MATCH($I$1,$A$1:$D$1,0))=$I$2),COUNTA(J$1:J1))),"")
下方向・↓
K,L,M,O列にコピー
 
N2
=IFERROR(INDEX(E:E,AGGREGATE(15,6,ROW($2:$5000)/(INDEX($A$2:$D$5000,,MATCH($I$1,$A$1:$D$1,0))=$I$2),COUNTA(J$1:J2)-1)+ROW()-MATCH(1,INDEX(0/(J$1:J2<>""),)))&"","")
下方向・↓
P列にコピー

トピックに返信