エクセルで他のシートから抽出しデーターを整理したいのですがご協力をお願いします。
画像のようなシートを作成しました。
それぞれaからADまでのデーターをシートAとシートBに振り分けて整理がしたい為
=INDIRECT("SheetA!A"&ROW(2:2))で行う方法で整理しました
そこから、さらに
シートAとシートBにそれぞれデーターを移したい。
元データーの記入欄は空欄になることもありそれぞれのシートに転記した場合空欄が出ることになり見にくい為それを上に詰めて整理がしたいがどういったことをすればよいか?
ご教授頂けましたら幸いです。
よろしくお願いします

No.5ベストアンサー
- 回答日時:
No4です。
>担当A列の方法もお教えいただけますと幸いです。
式を一度組み上げてしまうと、自分でも何が何やらわからなくなります。
原理的には、No3に回答した通りなのですが・・
もう少し考え方を変えて、式を短くする工夫をしました。
同時に、列チェックの方法を変更して、タイトル行の値でチェックするようにしてみましたので、AだのBだのを気にしなくても良くなることと思います。
まず、AE1セルに求めたいものと同じ「B」を入力しておきます。
その上で、AE2に以下の式を入力し、下方にフィルコピーします。
=IFERROR(INDEX(A$1:AC$23,MOD(AGGREGATE(15,6,(((INT((ROW(B$4:AC$23)-4)/4)*7+INT((COLUMN(B$4:AC$23)-2)/4))*100+COLUMN(B$4:AC$23))*100+ROW(B$4:AC$23))/(B$3:AC$3=$AE$1)/(MOD(ROW(B$4:AC$23),4)>0)/(B$4:AC$23<>""),ROW(A1)),100),MOD(INT(AGGREGATE(15,6,(((INT((ROW(B$4:AC$23)-4)/4)*7+INT((COLUMN(B$4:AC$23)-2)/4))*100+COLUMN(B$4:AC$23))*100+ROW(B$4:AC$23))/(B$3:AC$3=$AE$1)/(MOD(ROW(B$4:AC$23),4)>0)/(B$4:AC$23<>""),ROW(A1))/100),100)),"")
上の状態で、AE1セルの値を「A」に変えれば、3行目のタイトルが「A」となっている列が抽出対象となるようになります。
そもそも論になりますけれど、ご質問のような編集をなさりたいのであれば、初めからそのことを考慮したレイアウトにしておけば、もっと簡単にご質問内容は実現できると思います。
これまでの式のほとんどの計算が、点在するレイアウトから目的の位置をピックアップするために行っているものですから、元のデータが上手く整理されていれば不要な計算だと言えますので。
まったくの別法として、組み込み関数だけで無理矢理行わなくても、ユーザー定義関数を利用して、
=CalendarToColumn(B4:AC23, "B", 序数)
のような関数で求めるような方法も考えられると思います。
こちらの方が遥かに要領よく計算できますし、セル内の関数式もゴチャゴチャしなくで済むという利点もあるでしょう。
また、同じ内容を何度も考えるのは当方にとっても無駄な時間つぶしなので、このあたりでお終いにしておきます。

No.4
- 回答日時:
No3です。
>会社で使用したらエクセル2019で関数が使用できずでした。
>他に方法がありますでしょうか・・・・
ないことはないですけれど・・・・・
2019ではスピル機能は使えませんので、考え方も少し変える必要があります。
遊びでやってみはしましたが、自分でも意味がわからなくなります。
AE2セルに以下の式を入力して、AE2:AE106にフィルコピー。
=IFERROR(INDEX(B$4:AC$23,INT(INT(AGGREGATE(15,6,(INT((COLUMN(B$4:AC$23)-2)/4)+INT((ROW(B$4:AC$23)-4)/4)*7)*10+MOD(ROW(B$4:AC$23)-4,4)/(MOD(COLUMN(B$4:AC$23),4)=0)/(MOD(ROW(B$4:AC$23),4)>0)/(B$4:AC$23<>""),ROW(A1))/10)/7)*4+MOD(AGGREGATE(15,6,(INT((COLUMN(B$4:AC$23)-2)/4)+INT((ROW(B$4:AC$23)-4)/4)*7)*10+MOD(ROW(B$4:AC$23)-4,4)/(MOD(COLUMN(B$4:AC$23),4)=0)/(MOD(ROW(B$4:AC$23),4)>0)/(B$4:AC$23<>""),ROW(A1)),10)+1,MOD(INT(AGGREGATE(15,6,(INT((COLUMN(B$4:AC$23)-2)/4)+INT((ROW(B$4:AC$23)-4)/4)*7)*10+MOD(ROW(B$4:AC$23)-4,4)/(MOD(COLUMN(B$4:AC$23),4)=0)/(MOD(ROW(B$4:AC$23),4)>0)/(B$4:AC$23<>""),ROW(A1))/10),7)*4+3),"")
すごい量になりますね。ありがとうございます。使わせていただきますね。
ここまでくると担当A列はどのようにすればいいのか全く追いつけずちんぷんかんぷんになりますね
No.3
- 回答日時:
Np2です。
関数を探してみたら、やはり、もっと簡単な方法がありました。
AE2セルに
=TOCOL(INDEX(B4:AC23,INT((ROW(A1:A105)-1)/21)*4+MOD(ROW(A1:A105)+2,3)+2,INT(MOD((ROW(A1:A105)-1),21)/3)*4+3),1)
ですみますね。
(空白を省くだけならTOCOL関数で十分でした)
>Aも同シートにリストアップするためにはどこをいじればよいでしょうか?
式がかなり単純化されたので、わかりやすくもなっていると思います。
上式ではセル範囲に対してINDEX関数でリストアップする順に、3、7、11・・・列目を指定するようになっていますので、これを1、5、9・・・となるようにすればAに該当するようになります。
(式を単純化すると、INDEX(セル範囲, 行, 列) という式で、行、列にピックアップしたいセル位置を指定するようにしてあるだけです)
No.2
- 回答日時:
No1です。
せっかく補足を頂きましたが、見ても不明点が募るだけです。
なんだか無意味にセルの結合が行われているようだし、図の左にリストされている項目は左側の図には無いものがほとんどなので、どこからどう参照しているのかもよくわかりません。
そもそも、元のご質問文に示されている関数式が一体何なのかも不明のままです。
まぁ、リストアップができているのなら、FILTER関数で空白を除くのが手っ取り早いと思います。
勝手な推測をすると、「縦3セル分の項目をセットにして、カレンダーの日付順に横に折り返しながらZ字の順に読んでいって、そのうちの空白を除いて上に詰めてリスト化したい」というように感じますが、ご提示の式も説明もなんだかそれとは違うもののようにも思われます。
もしも上記だとすると、かなり複雑になってしまうので、手順を踏んで処理するのが簡単そうに思います。
1)補足の図の右側のリストを作成する
2)1)の結果をFILTER関数で空白を除いて上に詰める
のような手順にすれば大分簡略化できるでしょう。
補足の図のリストにご提示の状態が既にできているのなら、2)の部分だけで済むので更に簡単にできると思います。
試みに当方の勝手な解釈で、一発で出す式も考えてみましたが、それなりに複雑になります。
(以下は、スピル機能を使えるバージョンとして考えています)
(もう少し工夫すれば、簡略化できそうな気もしますけれど・・・)
添付図では、B3:AC23を元となるデータと考え、AE列にリストアップするとしています。
図では、AE2セルに
=FILTER(INDEX(B4:AC23,INT((ROW(A1:A105)-1)/21)*4+MOD(ROW(A1:A105)+2,3)+2,INT(MOD((ROW(A1:A105)-1),21)/3)*4+3),INDEX(B4:AC23,INT((ROW(A1:A105)-1)/21)*4+MOD(ROW(A1:A105)+2,3)+2,INT(MOD((ROW(A1:A105)-1),21)/3)*4+3)<>"","")
の式を入力してあります。
(該当する項目数に応じて、必要な範囲まで下方にスピルされます)

ありがとうございます。個人的には隣にリスト化するよりそれぞれAとBのシートにリストを見えればそれのみ見れるため当方としては楽であったという認識でした。
この方法でもすっきり見えそうです助かりました。
担当Bはこれでリスト化できますね。ありがとうございます。
大変恐縮ですが担当Aも同シートにリストアップするためにはどこをいじればよいでしょうか?
重ね重ね申し訳ありませんよろしくお願いいたします

No.1
- 回答日時:
こんばんは
図がよく見えないのと、ご質問文を読んでもサッパリですが・・
INDIRECT関数の必然性もよくわかりません。
>空欄が出ることになり見にくい為それを上に詰めて整理がしたい
実際の内容がよくわからないので、以下は勝手な推測ですけれど・・・
空欄を除きたい場合、スピル機能が使える環境ならFILTER関数を使えばできます。
例えば、質問文に記載の関数を例にするなら・・
=FILTER(SheetA!A2:A999,SheetA!A2:A999<>"","")
のような式を入力すれば、空白を除いた結果が下方にスピルされます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) ユーザー定義について質問です。 2 2023/06/28 13:21
- Excel(エクセル) Excelでのデータ管理 6 2022/12/24 09:33
- Excel(エクセル) 別シートに成約をボタン1つで転記したい 4 2023/10/26 16:38
- Excel(エクセル) VBAで、シート間の転記するコードを教えてください。 4 2023/03/26 10:43
- Excel(エクセル) 【Excel(エクセル)】複数シートで名簿管理したいです(数式?マクロ?) 3 2023/11/03 08:01
- Excel(エクセル) Excel 売上管理シートに入力した売上データを、日報に自動反映させたいと考えています。 売上管理シ 3 2023/04/29 18:08
- Excel(エクセル) Excelで日報を自動で作成したい 売上管理シートに入力した売上データを、日報に自動反映させたいと考 1 2023/04/29 18:07
- Excel(エクセル) INDIRECTを使わず excelで複数シートの同じセルを抽出したい 3 2024/04/12 12:20
- Excel(エクセル) Excelでポイントの管理表を作成したいです。 個人ごとにシートを作成し、発行者のシートでポイントを 3 2023/08/22 16:59
- Excel(エクセル) エクセルのマクロ 6 2024/01/24 10:00
このQ&Aを見た人はこんなQ&Aも見ています
-


エクセルでVLOOKUPの入ったセルをカウントしない方法
Excel(エクセル)
-


AM8:30から翌朝8:30まで勤務する消防士です 時間外勤務の管理を任されました 作成したEXCE
Excel(エクセル)
-


条件付き書式に設定する関数を教えて下さい
Excel(エクセル)
-
-
4

エクセルのデータ整理
Excel(エクセル)
-
5

エクセルで、数字ではない値(文字列)が入った場合の計算式を教えてください。
Excel(エクセル)
-
6

エクセルでAのセルに「家電」と入れたらDが「10」、「野菜」と入れたら「8」となる方法
Excel(エクセル)
-
7

【Excel】 1つのセルの日にちを4分割にしたい
Excel(エクセル)
-
8

UNIQUE関数、配列数式を使わずに品名ごとの集計を求めたい
Excel(エクセル)
-
9

エクセルの不調について
Excel(エクセル)
-
10

評価のエクセルを作りたいです。
Excel(エクセル)
-
11

Excelの数式について教えてください。
Excel(エクセル)
-
12

エクセルで作成した表の中にある数字大きい方からののランキングが関数でできますでしょうか 10代男性1
Excel(エクセル)
-
13

excelですが一つのマスに入ってる文字を2つのマスに変更できますか
Excel(エクセル)
-
14

エクセルで作成した書類の印刷について
Excel(エクセル)
-
15

Excel いい方法教えてください。
Excel(エクセル)
-
16

関数を教えて下さい
Excel(エクセル)
-
17

考えた式の戻り値が期待通りにならない
Excel(エクセル)
-
18

Excelの計算で差分を求める場合について
Excel(エクセル)
-
19

大容量があつかえるソフトを探しています
Excel(エクセル)
-
20

8:40までの出勤は全て8:30にする関数を教えて下さい!!
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
勤怠表について ABS、TEXT関数...
-
エクセルの関数について
-
Excelで4択問題を作成したい
-
エクセル
-
エクセルの表で作業してます。 ...
-
エクセル GROUPBY関数について...
-
エクセルの複雑なシフト表から...
-
【Officer360?Officer365?の...
-
エクセルについて
-
Amazonでマイクロソフトオフィ...
-
グループごとの人数のカウント
-
グループごとの人数のカウント
-
エクセルシートの見出しの文字...
-
グループごとの個数をカウント...
-
【マクロ】別ファイルへマクロ...
-
【マクロ】変数に入れるコード...
-
【マクロ】実行時エラー '424':...
-
ページが変なふうに切れる
-
9月17日でサービス終了らし...
-
エクセルのリストについて
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
9月17日でサービス終了らし...
-
エクセル
-
【マクロ】WEBシステムから保存...
-
エクセルの循環参照、?
-
エクセル ドロップダウンリスト...
-
エクセルのdatedif関数を使って...
-
特定のセルだけ結果がおかしい...
-
【マクロ】A列にある、日付(本...
-
【マクロ】EXCELで読込したCSV...
-
【マクロ】アクティブセルの時...
-
【エクセル】期限アラートについて
-
iPhoneのExcelアプリで、別のシ...
-
【関数】同じ関数なのに、エラ...
-
Excelの新しい空白のブックを開...
-
【マクロ】3行に上から下に並...
-
【マクロ】宣言は、何のために...
-
VBA チェックボックスをオーバ...
-
Excelについての質問です 並べ...
-
【マクロ】アクティブセルの2...
-
【関数】不規則な文章から●●-●●...
おすすめ情報









回答ありがとうございます
エクセルにカレンダーを作成その月の担当ABに対して1日最大3人仮にA'B'C'の処理を行う予定表を作りました。そのカレンダーには必ず3人担当するわけではなく空欄ができます。
別のシートBに一か月分の担当するA'B'C以下最大60名分をカレンダーに入力すれば縦1列に転記させ、空欄になる場所は上に詰めて見やすくしたいという意図になります
No.4様差し支えなければ担当A列の方法もお教えいただけますと幸いです。よろしくお願いいたします