エクセルに関する質問です。

質問

エクセルに関する質問です。
フィルターでデーダ抽出後に、例えばA列の２行目の文字を
別シートに表示させてい場合はどのようにすればよろしいでしょうか？

例）
シート１
　　A列　　B列　　C列
1
2　あ

シート２
　あ

回答No.6

Nouble

失礼しました
再度訂正
=IFERROR(
OFFSET(シート１!$A$1,
SMALL(INDEX((1-ISBLANK(シート１!$A$1:$A$999)*ISBLANK(シート１!$B$1:$B$999)*ISBLANK(シート１!$C$1:$C$999))*ROW(シート１!$A$1:$A$999)
+ISBLANK(シート１!$A$1:$A$999)*ISBLANK(シート１!$B$1:$B$999)*ISBLANK(シート１!$C$1:$C$999)*2^25,,),ROW(B1))-1,
COLUMN(B1)-1,1,1),
"")

未だ、今一…だけど
取りあえず「あ」は、でますね
横にゴミが、出るけど…　ね
参考URLみたいに

参考URL：

https://1drv.ms/x/s!AjviygfJDgV_kSSZsMUh9rauFdxS

回答No.5

imogasi

#2です。他の回答で、関数数式で回答が出ているのは、驚きました。
＝＝＝＝＝
＃２の補足。
その後ＶＢＡでこの点いろいろやってみて、＃２に関して補足。
ＶＢＡに関していうと
Sub test01()
Dim i As Integer, j As Integer
Dim myrng As Range, rng As Range
i = Range("A65536").End(xlUp).Row
MsgBox i
Set myrng = Range(Cells(1, 2), Cells(i, 2)).SpecialCells(xlCellTypeVisible)
myrng.Select
Selection.Copy Range("A" & (i + 3)) '元データの最下行以下であれば貼りつければそのまま
データが見える
End Sub
のように、画面上で、可視セルは捉えられる。
Set myrng = Range(Cells(1, 2), Cells(i, 2)).SpecialCells(xlCellTypeVisible)
myrng.Select
しかしこれらを、「シートの適当なセルにコピー貼り付けしないと」、可視セル内の行位置を指定できないようです。
「適当な」とは
（１）上記は同一シートの最下行次行以下のデータのない行領域に貼り付け
（２）前の回答では、空白の別シートに貼りつけました
いったん張り付ける方法は、うっとうしいので、採用されないでしょうが、可視セルだけでの相対セル位置指定の
Ｓｅｌｅｃｔｉｏｎ．Ｃｅｌｌｓ（３，１）のような、普通の状態での、従来的方式の延長での、捉え方は、（ＷＥＢ記事を調べてたが）できないようです。
（しかし、小生が知らないだけで、実は方法があるのかもしれませんが。）

回答No.4

Nouble

失礼しました、
間違えてました
訂正させてください。

=IFERROR(
OFFSET("シート１"!$A$1,
SMALL(INDEX((1-ISBLANK($A$1:$A$999)*ISBLANK("シート１"!$B$1:$B$999)*ISBLANK("シート１"!$C$1:$C$999))*ROW("シート１"!$A$1:$A$999)
+ISBLANK("シート１"!$A$1:$A$999)*ISBLANK("シート１"!$B$1:$B$999)*ISBLANK("シート１"!$C$1:$C$999)*2^25,,,),ROW(A1))-1,
COLUMN(A1)-1,1,1),
"")
ですね

何処かに書き込み
右へ2列フィルして、3列にし
此の3列を、必要なだけ
下向きに、フィルしてください

ところで、
NOT(ISBLANK((セル範囲))*ROW(同セル範囲)+NOT(ISBLANK((セル範囲))*COLUMN(同セル範囲)/1000000
という式、ですが

=IFERROR(OFFSET("シート１"!$A$1, INDEX(SMALL(NOT(ISBLANK("シート１"!$A$1:$C$999))*ROW("シート１"!$A$1:$C$999)+NOT(ISBLANK("シート１"!$A$1:$C$999))*COLUMN("シート１"!$A$1:$C$999)/1000000+ISBLANK("シート１"!$A$1:$C$999)*2^25,,)-1,
ROW(A1)),"")

適当なセルに、書き込み
下に、フィル
してみてください

回答No.3

Nouble

長文ですが、
お邪魔します。

失礼ながら
そう複雑なもの
ではない
ですね

1、要は空白でない行を特定し
2，其のリストを得て
3，一元的に参照、表示、
すれば良い
でしょう。

では、少しずつ
此等を、見ていきます。

1、ですが、
基本なので
詳しい目に、説明します

で、
空白では無い行
とは、
「空白しか含まれない行」では無い
と、言う事であり

故に、
行内に含まれる、指定列内の
一行の、全てのセルが
全て空白か
調べれば、判ります
よね？

空白かどうかを知るには
ISBLANK関数が便利です

此の関数は
空白だとTRUE
空白でないとFALSE
を、返します。

今回は
A、B、C、列ですか？

一例として
A1:C1が
全て空白か、知りたい時は
論理演算関数、ANDを用いて
=AND(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1))
等とすれば、判ります

此で、TRUEが返された
なら
其の範囲は、全て空白
です

ところで、
Excelには
配列数式
と、言う機能が
あります

どういう機能か
と、言うと
超簡単に、簡略化した言い方で
言うと

通常は、ひとセルしか　扱いませんが
此を、複数セル指定します

すると、
其の此処のセル毎に、個々個別に
関数を適応し
列記形式で、解答を返す
と、言うもので

一例としては
先程は、ISBLANK(A1)
と、指定したところを
一気に、ISBLANK(A1:C1)と、指定します
すると、
{TRUE;TRUE;TRUE｝
とか、返して貰えます

もう一つ、
Excelには
値を、見なし補完する機能があり

TRUE を0と
0をTRUEと
FALSEを1と
ABS(SIGN(0以外の値))をFALSEと
見なしてくれます

ので、
例えば、
{TRUE,TRUE,TRUE}+0
と、すると
{TRUE+0,TRUE+0,TRUE+0}
と、いう演算に　展開され
本来、論理値と数値は
演算不能…　なので

TRUE が0に、見なし置換され
{0+0,0+0,0+0｝
と、変形されます

結果、
{0,0,0}
と、返されます

此処で実は、
余談ですが
Excelでは、仕様上
表示は、1つの値しか　しません

しかし
{0,0,0｝
には、値が三つ　含まれます

ので、
表示上は
エラーになる事が、あります

逆に言えば、
表示上、エラーだから
と、演算失敗だな
と、取るのは
早計な、訳です

話を戻すと、
{0,0,0｝で、なくても
先程の
ISBLANK (A 1 :C1)+0
と、しても
同様に
{0,0,0｝
と、返して　くれます

また、
SIGN(SUM(ISBLANK(A1:C1)+0))
と、すると
全て空白の場合
SIGN(SUM( {0,0,0}))
と、なり
0が、返されますし、

例えば、
A1と、B1が、空白でない場合
SIGN(SUM({1,1,0})
と、なり
SIGN(2)
と、なり
1が返されます

1又は0ですので
其のセル位置の行数を返す関数として
ROW(セル位置)
と、言う関数がある
の、ですが

0に何を掛けても0
1に何を掛けても、掛けた数
ですので、

SIGN(SUM(ISBLANK(A1:C1)+0)) *ROW(A1)
と、すると

A行に、少なくとも一つ
値があれば
A1:C1の行数である
1が、返されます

同様に
SIGN(SUM(ISBLANK(A3:C3)+0)) *ROW(A3)
と、すると

A3:C3の行数である
3が、返されます

でも、此は
実は使い難いもの
でして

(1-ISBLANK(A1)*ISBLANK(B1)*ISBLANK(C1))*ROW( a1)
等が、実用的ですね

つぎに、2、ですが、
A1等としたセル位置の指定を
例えば、
A1:A999と、セル範囲指定にします

具体的には
(1-ISBLANK(A1:A999)*ISBLANK(B1:A999)*ISBLANK(C1:A999))*ROW( a1:A999)

しかし、此のままでは
所々、
0が、挟まれます

とても邪魔、なので
空白セルは
0ではなく、
都合で、其のExcel環境最大行数より
大きい数にします

また、本来の参照先である
シート１の設定をし
参照を固定して
例えば、
(1-ISBLANK(シート１"!$A$1:$A$999)*ISBLANK(シート１"!$B$1:$B$999)*ISBLANK(シート１"!$C$1:$ C$999))*ROW($A$1:$A$999)
+ISBLANK("シート１"!$A$1:$A$999)*ISBLANK($B$1:$B$999)*ISBLANK("シート１"!$C$1:$C$999)*2^25
と、してみます

此を
昇順にSMALL関数で、並べ直し
同時に
空白でない行分
取り出せば
リスト化できます

ですが、
此の、「空白でない行分」て
未だ、説明してない
ですね

(1-ISBLANK("シート１"!$A$1:$A$999)*ISBLANK("シート１"!$B$1:$A$999)*ISBLANK("シート１"!$C$1:$A$999))

此の式で
空白行は0
空白でない行は1

な、訳ですから
此を総和すれば、判りますね

今回は
ROW(INDIRECT("A1:A"&SUM((1-ISBLANK("シート１"!$A$1:$A$999)*ISBLANK("シート１"!$B$1:$B$999)*ISBLANK("シート１"!$C$1:$C$999))))
…
と、したい所
ですが、
実は、Excelでは
SUM関数に、癖があり
誤動作する可能性が、あります

ので、
SUMを、SUMPRODUCTに、
差し替えます

結果の
リストを得る式
ですが

SMALL(INDEX((1-ISBLANK("シート１"!$A$1:$A$999)*ISBLANK("シート１"!$B$1: $B$999)*ISBLANK("シート１"!$C$1:$C$999))*ROW($A$1:$A$999)
+ISBLANK("シート１"!A$1:$A$999)*ISBLANK("シート１"!$B$1:$B$999)*ISBLANK("シート１"!$C$1:$C$999)*2^25,,,),ROW(INDIRECT("A1:A"&SUMPRODUCT((1-ISBLANK("シート１"$A$1:$A$999)*ISBLANK("シート１"!$B$1:$B$999)*ISBLANK("シート１"!$C$1:$C$999)))))
と、します

3、ですが、
OFFSETを遣うと良い
と、思います

先程の式を、少し変形して
OFFSET("シート１"!$A$1,
SMALL(INDEX((1-ISBLANK(A1:A999)*ISBLANK(B1:B999)*ISBLANK(C1:C999))*ROW( A1:A999)
+ISBLANK(A1:A999)*ISBLANK(B1:B999)*ISBLANK(C1:C999)*2^25,,,),ROW(A1))-1,
0,1,1)

エラー処理を加えて
IFERROR(
OFFSET("シート１"!$A$1,
SMALL(INDEX((1-ISBLANK(A1:A999)*ISBLANK(B1:B999)*ISBLANK(C1:C999))*ROW( A1:A999)
+ISBLANK(A1:A999)*ISBLANK(B1:B999)*ISBLANK(C1:C999)*2^25,,,),ROW(A1))-1,
0,1,1),
"")
ですね

ところで、
NOT(ISBLANK((セル範囲))*ROW(同セル範囲)+NOT(ISBLANK((セル範囲))*COLUMN(同セル範囲)/1000000
を、芯として
式を、組み立てると

もっと、高度な事が出来ます

さて、
此処からは、余談ですが
エクセルが
其のリレーショナルな真価を
用いられず
ただのテキストエディタ
的に遣われている
のを、見るにつけ
「あはれ」さが、目に余ります。
（※注：哀れ、ではなく　あはれ）

私は、
有的なリレーショナルを
アクセス程の手間要らずで
比較的小規模なデータに
手軽に遣えてこそ
其の価値がある
と、考えます。

VBAにする位なら
アクセスでクエリから
レポートを出させれば良いのでは？…

んー
言い過ぎですね

回答No.2

imogasi

エクセルと言えば関数だが、フィルタ結果の、可視セルを対象に関数で処理することはできないと思う。
知らないのだろうが、むつかしい問題に踏み込んだのではないか。
ーー
関数でどうしてもやりたいなら、フィルターと表現せず、フィルタ条件と同じ条件で抜出
し、抜出結果のシートのデータで考えることになろう。
ーー
エクセルシートのデータを、条件をかけて抜き出すのは、式が複雑になる。エクセルには不向きなタイプであることを知ってますか。
Ｇｏｏｇｌｅで「エクセル 関数で抜き出し」で照会
https://oshiete.goo.ne.jp/qa/3200952.ｈｔｍｌなど時々出る質問。
私は方法として、関数にこだわるのはよくないと思う。
ーー
例データ　質問と違う例だが
Ａ１：Ｂ９
項目１ 項目２
あ aa
い bb
き cc　　＜－－ここがフィルタの結果の第1行データになる
う dd
え aa
か aa
き cc
く cc
Ｂ列の「cc」でフィルタ
ーー
結果
見た目　Ｓｈｅｅｔ２のＡ１：Ｂ４
項目１ 項目２
き cc
き cc
く cc
ーー
これをＶＢＡで処理して
Sub test01()
Set Rng = Worksheets("Sheet1").Range("A1:b20")
Rng.AutoFilter _
Field:=2, _
Criteria1:="=" & "cc"
Rng.SpecialCells(xlCellTypeVisible).Copy
Worksheets("Sheet2").Activate
Range("A1").Select
ActiveSheet.Paste
MsgBox Worksheets("Sheet2").Range("a1").Cells(2, 1)
End Sub
フィルタ結果の可視セルのコピーをＳｈｅｅｔ２に張り付けている。
そしてWorksheets("Sheet2").Range("a1").Cells(2, 1)
で２行目第1列をCells(2, 1)をＭｓｇｂｏｘで表示している。
結果は「き」
ーー
操作で考えるとすると、Ｓｈｅｅｔ１でフィルタ結果である可視セルをコピーする。やり方は、
ジャンプーセル選択ー可視セルーコピーー別シートのセル選択ー貼り付け
ここで＞例えばA列の２行目の文字を、選択する。関数でモテ来るなら＝Ｓｈｅｅｔ２！Ａ２のようになる。

回答No.1

中京区 桑原町（@l4330）

別シートの表示したいセルに下記の式を入力してください
=Sheet1!A2

ただし、シート1の名が「Sheet1」の場合
別の方法は、
1)シート2で「あ」を表紙したいセルをクリック
2)「=」をキーボードから入力
3)マウスでシート1のA2セルをクリック
4)Enter