
エクセルで他のシートから抽出しデーターを整理したいのですがご協力をお願いします。
画像のようなシートを作成しました。
それぞれ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も見ています
-
あなたの「必」の書き順を教えてください
ふだん、どういう書き順で「必」を書いていますか? みなさんの色んな書き順を知りたいです。 画像のA~Eを使って教えてください。
-
歳とったな〜〜と思ったことは?
歳とったな〜〜〜、老いたな〜〜と思った具体的な瞬間はありますか?
-
コンビニでおにぎりを買うときのスタメンはどの具?
コンビニでおにぎりを買うとき、何の具材を選ぶことが多いですか?
-
AIツールの活用方法を教えて
みなさんは普段どのような場面でAIツール(ChatGPTなど)を活用していますか?
-
【大喜利】【投稿~1/20】 追い込まれた犯人が咄嗟に言った一言とは?
【お題】追い込まれた犯人が咄嗟に言った一言とは?
-
エクセルでVLOOKUPの入ったセルをカウントしない方法
Excel(エクセル)
-
AM8:30から翌朝8:30まで勤務する消防士です 時間外勤務の管理を任されました 作成したEXCE
Excel(エクセル)
-
エクセルのデータ整理
Excel(エクセル)
-
-
4
エクセルで、数字ではない値(文字列)が入った場合の計算式を教えてください。
Excel(エクセル)
-
5
条件付き書式に設定する関数を教えて下さい
Excel(エクセル)
-
6
エクセルでAのセルに「家電」と入れたらDが「10」、「野菜」と入れたら「8」となる方法
Excel(エクセル)
-
7
UNIQUE関数、配列数式を使わずに品名ごとの集計を求めたい
Excel(エクセル)
-
8
評価のエクセルを作りたいです。
Excel(エクセル)
-
9
【Excel】 1つのセルの日にちを4分割にしたい
Excel(エクセル)
-
10
エクセルで作成した表の中にある数字大きい方からののランキングが関数でできますでしょうか 10代男性1
Excel(エクセル)
-
11
Excel いい方法教えてください。
Excel(エクセル)
-
12
関数を教えて下さい
Excel(エクセル)
-
13
エクセルの不調について
Excel(エクセル)
-
14
Excelの数式について教えてください。
Excel(エクセル)
-
15
エクセルで作成した書類の印刷について
Excel(エクセル)
-
16
excelですが一つのマスに入ってる文字を2つのマスに変更できますか
Excel(エクセル)
-
17
表中の指定した条件の文字列を結合する方法について
Excel(エクセル)
-
18
考えた式の戻り値が期待通りにならない
Excel(エクセル)
-
19
Excelの計算で差分を求める場合について
Excel(エクセル)
-
20
条件付き書式の効率的な設定の仕方について
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・思い出すきっかけは 音楽?におい?景色?
- ・あなたなりのストレス発散方法を教えてください!
- ・もし10億円当たったら何に使いますか?
- ・何回やってもうまくいかないことは?
- ・今年はじめたいことは?
- ・あなたの人生で一番ピンチに陥った瞬間は?
- ・初めて見た映画を教えてください!
- ・今の日本に期待することはなんですか?
- ・【大喜利】【投稿~1/31】『寿司』がテーマの本のタイトル
- ・集中するためにやっていること
- ・テレビやラジオに出たことがある人、いますか?
- ・【お題】斜め上を行くスキー場にありがちなこと
- ・人生でいちばんスベッた瞬間
- ・コーピングについて教えてください
- ・あなたの「プチ贅沢」はなんですか?
- ・コンビニでおにぎりを買うときのスタメンはどの具?
- ・おすすめの美術館・博物館、教えてください!
- ・【お題】大変な警告
- ・【大喜利】【投稿~1/20】 追い込まれた犯人が咄嗟に言った一言とは?
- ・洋服何着持ってますか?
- ・みんなの【マイ・ベスト積読2024】を教えてください。
- ・「これいらなくない?」という慣習、教えてください
- ・今から楽しみな予定はありますか?
- ・AIツールの活用方法を教えて
- ・最強の防寒、あったか術を教えてください!
- ・【大喜利】【投稿~1/9】 忍者がやってるYouTubeが炎上してしまった理由
- ・歳とったな〜〜と思ったことは?
- ・モテ期を経験した方いらっしゃいますか?
- ・好きな人を振り向かせるためにしたこと
- ・スマホに会話を聞かれているな!?と思ったことありますか?
- ・それもChatGPT!?と驚いた使用方法を教えてください
- ・見学に行くとしたら【天国】と【地獄】どっち?
- ・これまでで一番「情けなかったとき」はいつですか?
- ・この人頭いいなと思ったエピソード
- ・あなたの「必」の書き順を教えてください
- ・14歳の自分に衝撃の事実を告げてください
- ・人生最悪の忘れ物
- ・あなたの習慣について教えてください!!
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【マクロ】シート追加時に同じ...
-
【マクロ】Call関数で呼び出し...
-
別のシートの指定列の最終行を...
-
システムファイルについて
-
エクスプローラーで見ることは...
-
スプレッドシートでの数値集計
-
Vba アドインソフトのバージョ...
-
Excel関数の解決方法
-
Excelのデーターバーについて
-
自動的に日付入力 応用
-
エクセルの設定、下へスクロー...
-
エクセルの数式につきまして
-
エクセルの設定、特定の列以降...
-
UNIQUE関数の代用
-
【マクロ】表における同じお客...
-
カーソルを合わせてる時のみ行...
-
Excelでプルダウン選択(I1セル)...
-
エクセルの数式について教えて...
-
エクセルの数式について教えて...
-
エクセル 同じ行の隣り合う数字...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【マクロ】重複する同じ行を、...
-
Excelの条件付き書式のコピーと...
-
vba 印刷設定でのカラー印刷と...
-
VBA の単語の意味を教えて下さい。
-
Excel 日付の表示が直せません...
-
エクセル 同じ行の隣り合う数字...
-
エクセル条件付き書式について。
-
エクセルの数式につきまして
-
ファイル名の変更
-
エクセル 数字のみ抽出につて
-
Excelの開始ブックを固定したい...
-
エクセルの数式について教えて...
-
エクセルのセルをクリックする...
-
=INDIRECT(RIGHT(CELL("filenam...
-
エクスプローラーで見ることは...
-
Excelの関数で質問です
-
至急お願いいたします 屋上の備...
-
エクセルでセルに入力する前は...
-
関数を教えて下さい
-
Excel 関数での質問です
おすすめ情報
回答ありがとうございます
エクセルにカレンダーを作成その月の担当ABに対して1日最大3人仮にA'B'C'の処理を行う予定表を作りました。そのカレンダーには必ず3人担当するわけではなく空欄ができます。
別のシートBに一か月分の担当するA'B'C以下最大60名分をカレンダーに入力すれば縦1列に転記させ、空欄になる場所は上に詰めて見やすくしたいという意図になります
No.4様差し支えなければ担当A列の方法もお教えいただけますと幸いです。よろしくお願いいたします