人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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

1206174892
●拡大する

●質問者: masshie
●カテゴリ:コンピュータ 科学・統計資料
✍キーワード:Excel クエリ セル マクロ 関数
○ 状態 :終了
└ 回答数 : 5/5件

▽最新の回答へ

1 ● heart-rhythm
●20ポイント

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

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

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

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

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

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

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

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

◎質問者からの返答

なるほど。

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

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


2 ● a2gi
●40ポイント

作ってみました。

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

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

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

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

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

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

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

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

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

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

◎質問者からの返答

ありがとうございます。

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

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

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

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

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


3 ● heart-rhythm
●1ポイント

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


(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ならば、マクロを多少追加しないといけないですが、ワークシートを開くたびに最新の情報に自動更新されます。

◎質問者からの返答

(笑)


4 ● y3kz
●50ポイント
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セルを右方にオートフィル。




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

◎質問者からの返答

ありがとうございます。

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

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


5 ● SALINGER
●50ポイント

作業列を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))

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

◎質問者からの返答

ありがとうございます。

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

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ