1206174892 Excelのシート1(奥)から、シート2(手前)の色つき部分を生成するワークシート関数はできますか?

B列(区画) が "A" であるものを A列(どうぶつ) から抜き出して、シート2の1行目に並べたいです。セルの色つけは問題には含まれません。
マクロは使用したくありません。ワークエリアを使用することはかまいません。

回答の条件
  • 1人2回まで
  • 登録:2008/03/22 17:34:54
  • 終了:2008/03/23 01:53:07

回答(5件)

id:heart-rhythm No.1

heart-rhythm回答回数32ベストアンサー獲得回数12008/03/22 17:55:27

ポイント20pt

こういうことを求めていらっしゃるのかわかりませんが、一応できました。

(1)1行目を選択します。

(2)[データ]-[フィルタ]-[オートフィルタ]を選択します。

(3)"区画"のところに出てくるドロップダウンボタンをクリックして"A"を選択します。

(4)区画がAのものだけが表示されるので、そのセルをすべて選択して、コピーします。

(5)新しいシートを開き、そこで[編集]-[形式を指定して貼り付け]を選択します。

(6)貼り付けの形式を"値"とし、[行列を入れ替える]にチェックします。←ここがミソ

(7)不必要なA列と2行目をクリアして、動物名のセルの色を指定します。

id:masshie

なるほど。

結果がリアルタイムに変更される方法でお願いします。

シート1は頻繁に変更され、前回ファイルを開けた時から変更されているかどうかも分からないという前提で。

2008/03/22 18:12:35
id:a2gi No.2

a2gi回答回数81ベストアンサー獲得回数32008/03/22 19:19:19

ポイント40pt

作ってみました。

Yahoo!ブリーフケース - 一覧表示

公開フォルダの「動物」というExcelのファイルです。

長ったらしいですが仕様としては

Sheet1の100行までは探せるようにしました。(適宜修正してください)

Sheet2のA1セルは区分Aの数を数えています。

これで区分Aの数が変わっても対応しています。

なお、1行目はG列目まで数式を入れてあるのでこれも適宜調節してください。

(必要な数だけ数式を入力するにはさすがにマクロを使わないと無理)

あと、余計なところに数式は言ってますが消すの忘れただけなので適当に消して置いてください。

何かあれば補足お願いします。

id:masshie

ありがとうございます。

どこが意味のある式が入ったセルなのか見つけるのに苦労しましたが...

式が無駄に複雑すぎるような気がします。

ただ、答えの数を数えてしまうのは好きです。こうすると、

if(isna(とっても長い式),"",とっても長い式ふたたび)

のようにならなくて済むのでいい感じです。

2008/03/23 01:34:14
id:heart-rhythm No.3

heart-rhythm回答回数32ベストアンサー獲得回数12008/03/22 21:06:22

ポイント1pt

次の方法ではいかがでしょうか?


(1)列A,Bを選択してピボットテーブルを新しいワークシートに作成する。(とりあえず、レイアウトは指定しません。)

(2)新しいワークシートのピボットテーブルの列見出しに「区画」「動物」の順番で項目をドラッグ&ドロップする。

(3)「区画」でAのみを選択すると、区画がAの動物のリストが並びます。

(4)「区画」と「動物」の集計を”なし”に設定します。

(5)不必要な行、列をそれぞれ非表示にします。

-> 具体的には列A、行3、列5以外の余計な表が表示されている列

(6)ツールバーには「ピボットテーブルツールバー」を表示しておきます。

(7)動物と区画のデータを変更したら、「ピボットテーブルツールバー」の赤いのボタン(更新)をクリックされると、列見出しが更新されます。



で、ここからは運用で避けるか、マクロを利用するかですが、


(案1)運用ルールで逃げる

必ず(ピボットーテーブル更新)ボタンをクリックするものとする。


(案2)ここだけマクロで逃げる

(1)[ツール]-[マクロ]-[新しいマクロの記録]を選択し、”データ更新”というマクロ名を設定して、データ更新ボタンをクリックしてマクロを停止する。

(2)[ツール]-[マクロ]-[マクロ]で、"データ更新"を選んで編集する。

(3)ピボットテーブルのあるシートを選択して、下記のプログラムを入力する。


Private Sub WorkSheet_Activate()

Module1.データ更新

End Sub



#案1ならば、マクロを書かずに済みますが、毎回更新ボタンをクリックしないといけません。

#案2ならば、マクロを多少追加しないといけないですが、ワークシートを開くたびに最新の情報に自動更新されます。

id:masshie

(笑)

2008/03/22 22:27:59
id:y3kz No.4

y3kz回答回数31ベストアンサー獲得回数92008/03/22 21:27:49

ポイント50pt
1.まず、Sheet2で…

 (1) A1セルに"A"を記入。(抽出対象の指定です。"B"を抽出したくなったらこのセルに"B"を入れます。)


2.次に、Sheet1で…

 (1) C2セルに次の式を記入。(B列の値がSheet2のA1セルに一致したとき、それが上から何回目の一致なのかを表示します。)

=IF(B2=Sheet2!A$1,COUNTIF(B$2:B2,Sheet2!A$1),0)

 (2) D2セルに次の式を記入。(あとでvlookup関数を使うので、A列を右側にコピーしておきます。)

=A2

 (3) C2:D2を選択して、下方にオートフィル。


3.もう一度 Sheet2に戻って…

 (1) B2セルに次の式を記入。(列番号に応じて、さきほどのC:D列から該当する値を探します。)

=VLOOKUP(COLUMN()-1,Sheet1!$C:$D,2,FALSE)

 (2) B2セルを右方にオートフィル。




…以上でいかがでしょうか。

id:masshie

ありがとうございます。

countif & vlookup 方式。シンプルでグー。

countif で何回目の出現かを数えるのがミソですね。

2008/03/23 01:46:10
id:SALINGER No.5

SALINGER回答回数3454ベストアンサー獲得回数9692008/03/22 21:44:03

ポイント50pt

作業列を1行追加するとして、例えばC列とします。

C2セルの数式を

=IF(B2="A",C1+1,C1)

にして下にコピー

そして抽出するシートのB1セルの数式を

=IF(ISNA(INDEX(Sheet1!$A:$A,MATCH(COLUMN()-1,Sheet1!$C:$C,0),1)),"",INDEX(Sheet1!$A:$A,MATCH(COLUMN()-1,Sheet1!$C:$C,0),1))

にして右にコピーという方法もあります。

id:masshie

ありがとうございます。

countif(相当) + index ← match 方式。なかなかデス。

2008/03/23 01:49:13
  • id:a2gi
    私のやつはSheet2の1行目以外は全て消して大丈夫ですよ。
    Sheet2のA1セル以外余計なセルを使わないでやるとあれくらい長ったらしい式になります。
    まぁSheet2のcountif関数にしても長ったらしい1行目のB列以降の$A$1を置き換えればそれこそ
    余計なセルを一つも使わずに出来ますけどね。
    無駄に複雑・・・
    まぁわかりやすくするなら3行に分けるものを1行にまとめていますからね。
  • id:masshie
    ポイント
    - countif でピックアップすべき値が出現したことをカウントする。
    - 別の列から対応する値をとるには、vlookup か、match と index を組み合わせ。vlookup では目的の列の順番になる表を生成する。
    - 縦のものを横にするには、column() の結果で vlookup/index をひく。

    ありがとうございました。
  • id:kaiton
    気付いたときは終わっていたので、ID:SALIGERさんの回答にも似ていますが別解を
    C1に抽出する区画を 例では A
    C2=IF(B2=$C$1,ROW(),"") 下へコピー
    抽出結果の方は
    B1=INDEX(Sheet1!$A$1:$A$8,SMALL(Sheet1!$C$2:$C$8,COLUMN()-1)) 右へコピー

    条件付き書式の「数式が」で =ISERROR(B1) で書式は 文字色を 白

    条件付き書式の変わりに
    B1の式で IF(ISERROR(SMALL(Sheet1!$C$2:$C$8,COLUMN()-1)),.. や
    IF(COLUMN()-2<COUNT(Sheet1!$C$2:$C$8),.. としてもいいかもしれません。

この質問への反応(ブックマークコメント)

トラックバック

  • VBAのお勉強 VLOOKUPワークシート関数でいろいろ simple blog いろいろ勉強中 2008-03-22 21:58:09
    http://q.hatena.ne.jp/1206174892 「ワークシート関数だけでやる」ということで、「VBAのお勉強」じゃないですが(^^;) 質問者さんの「ワークエリア」という言葉の意味がちょっとわからなかったの...
「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません