別シートに任意のセルを転記する方法について

質問

縦に6行ずつのデータがあり、これを横1行の別シートに転記する際に、
以前こちらで回答頂いた方法を応用したいと考えています。

=IF(INDEX(Sheet1!$Z:$Z,(ROW(A1)-1)*6+COLUMN(A1))=0,"",INDEX(Sheet1!$Z:$Z,(ROW(A1)-1)*6+COLUMN(A1)))

アドバイスのとおり、上記の数式で見事に横にデータが転記できました。社員1人につき6行ずつのデータが縦にならんでおり、これを別の社員1人あたり1行で横に並んだデータにしたいというものでした。
1人目のデータは1行目を1列目に、2行目を2列目に・・・2人目のデータである7行目を1列目に、8行目を2列目に・・・3人目は13行目を1列目に・・・となります。

しかし、順次並べるのではなく、転記の必要にないデータを含むシートの任意のセルを選び、別シートの任意のセルへ転記する必要が生じたため、悩んで路頭に迷っております。。。

例えば、6行ずつのデータのうち、いつも3行目を別シートの5列目に。4行目は転記せずに、5行目を6列目に。また、6行目を7列目に転記せずに10列目に転記する。（8・９列目は、別データを入力するため空白にしたい）
そして、社員2人目である7行目からは、上記と同じ規則で転記したい。

などというように、選んで転記する方法は何かありますでしょうか？？

=INDEX(Sheet1!$Z:$Z,(ROW(B2)-ROW($B$2))*6+IF(COLUMN()=4,MOD(COLUMN(B2)-1,6)+1,MOD(COLUMN(B2)-1,7)))　のようにしても上手くいきません。
何卒宜しく御願いいたします。

回答No.4

nonamochi · ベストアンサー

気に入って頂けてうれしいです（笑）
出来るだけ単純にした方が応用がききます。

>sheet2のE4セルに=OFFSET(Sheet1!$BT$11,(ROW()-1)*9,0)と入力

これで行くと
row()関数で4が返ります（つまりrow関数は4行目に設定されている）
それが-1されて９倍＝27
それに11が加わるので、最終的に38行目を参照する事になります。

E4から初めるのであれば、E4の時にBT11を参照しなければならないので、
ROW()-1)*9の部分はROW()-4)*9とすればいいのでは？

お礼

できました！！！！
なるほど！ご丁寧に教えて頂いたおかげで、最初からあきらめていたところも応用できました。
思っていた以上の仕上がりになり、大変満足しています。
ありがとうございました！！
何度もお付き合い頂き、また丁寧にご指導頂き、本当にありがとうございました！！

回答No.3

nonamochi

こんにちは

１人当りのデータが10項目程度であれば、シート#2の1行目のみ手作業で計算式を設定し、それを下方向に一気にコピーをするのではダメですか？
いちいちシート#2を作るのが面倒であれば、予め十分な領域を持った計算式を設定したテンプレートを作成しておき、そのシートを適宜コピーして使えばいいです。
シート#1のデータの入っていないセルを参照すると、シート#2には0が表示されますが、これが嫌ならオプションで0を表示しない様に設定すればいいです。

非常に単純なやり方で笑われそうですが、もし勘違いしている様なら指摘してください。

例として、
シート#1のA1から下方向に
１人目：データ1，データ2・・・・・、データ6、データ7
２人目：データ1，データ2・・・・・、データ6、データ7
：
X人目：データ1，データ2・・・・・、データ6、データ7

シート#2の計算式は
A1=OFFSET(Sheet1!$A$1,(ROW()-1)*7,0)
B1=OFFSET(Sheet1!$A$2,(ROW()-1)*7,0)
C1=OFFSET(Sheet1!$A$3,(ROW()-1)*7,0)
：
G1=OFFSET(Sheet1!$A$7,(ROW()-1)*7,0)

当然ながら、$A$1のうち、行に当たる部分を変えれば好きな行を好きな列に表示できます。
例えば、
A1=OFFSET(Sheet1!$A$1,(ROW()-1)*7,0)
B1=OFFSET(Sheet1!$A$3,(ROW()-1)*7,0)
C1=OFFSET(Sheet1!$A$5,(ROW()-1)*7,0)
D1=OFFSET(Sheet1!$A$2,(ROW()-1)*7,0)

補足

こんにちは！！

すごい！
近づいてきたようです。INDEX関数よりも単純にできそうなんで興奮気味です！
が、
OFFSET関数に不慣れなもので、、実際に当てはめてみたところ、以下のようになります。

実際の数式を表示しますと、

合算表のE4にsheet1のBT11セルを転記する：
sheet2のE4セルに　=OFFSET(Sheet1!$BT$11,(ROW()-1)*9,0)
と入力し、これをE列へ下へドラッグ。

おそらく社員４人目からのデータが入りました。これは、スタートがE1ではなく、E4のためでしょうか？

OFFSEF関数の２番目の引数は、基準セルから何行目か、ということですよね？？
であれば、基準セルの設定を変えれば出来そうなのですか、、、いかがでしょうか。

回答No.2

nonamochi

こんばんは

まだ良く見えないんですが・・・・
元シートから別シートへ転記するパターンは複数あったとしても、元シート全体については同一の転記パターンを適用しますか？
それとも、元シートの途中で転記パターンを変更しますか？
例えば社員1～10までは転記パターン１
社員10～20までは転記パターン２
と言った形にはならないですか？

補足では「全員同じ規則性を保つ」と書かれていますが、そもそもの質問には「任意のセルを選び、別シートの任意のセルへ転記する」と書かれていますので、矛盾がある様に思えるんですが。

補足

おはようございます。
御返信ありがとうございます。

シートの途中で、パターンが変わる事はありません。
また、所属ごとに元シートは複数ありますが、全て同じフォーマットです。
シートが変われば所属も変わりますので、抽出するデータが増えたりする場合があるかも知れませんが、その場合はまた別の列に転記します。
基本的には、同じ項目のデータ（同じ行にあるデータ）は別シートの合算表の同じ列に転記します。

「任意」としたのは、上記のように元シート毎に抽出するデータが増減する可能性があるため、抽象的に申し上げました。うまく説明できずに申し訳ありません。

回答No.1

nonamochi

こんにちは

「任意のセル」「例えば」と言う説明から判断すると、
書かれた内容はあくまでも例であり、状況に応じて変化する。しかも対象となる社員もいつも同じとは限らず、何人目になるかもわからない
と、言う判断でよろしいでしょうか？
ここまで汎用性をもたせるとなると、関数では処理しきれないです。VBAでプログラム化する必要があります。

skybluechannelさんがどの様な立場の人で、どの様な処理をしたいのかがわかりませんが、場合によってはエクセルでは無く、アクセスを使用した方がデータの抽出とまとめが柔軟に行えます。ただし、慣れないうちはかなり戸惑うかとおもいますが。

補足

ご返信に感謝致します！！
あえて「例えば・・・・」とさせて頂いたのは、何行目をどこに転記させるか今だ作成中ですので確定できていないためです。
当方は、管理部門に所属しており、具体的には、毎月の勤怠管理のための表です。
元シートにある社員はすべて対象で、どこに何を転記させるかは全員同じ規則制を保ちたい。
簡単にいうと、所属ごとのシートで管理している1ヶ月の勤怠集計を、さらに全所属合算の表に抽出したいのです。今までは、全所属合算の表に、所属毎に仕上げた集計表から必要な数字を手書きで転記しておりましたが、ここの作業を簡略化できないかと思ったわけです＞＜

元シートの　　　　　　　　　　　　　　　　　　　　別シートの
1行目（11行目）には出勤日数　　　　　　　　　　　→B列に
2行目（12行目）には公休合計日数、　　　　　　　　→C列に
3行目～5行目（13～15行目）には公休の種類内訳　　→転記の必要なし
6行目（16行目）には有給使用日数　　　　　　　　　→D列
7行目（17行目）には欠勤日数　　　　　　　　　　　→F列　※E列はとばして空白
8行目（18行目）には特別休暇日数　　　　　　　　　→G列
9行目（19行目）には残業時間　　　　　　　　　　　→J列
10行目（20行目）には休日残業時間　　　　　　　　 →K列

※社員1人あたり10行使用した場合（）は社員2人目。

エクセルでの管理を希望しますが、やはり難しいでしょうか・・・

宜しく御願い致します。