[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『月毎のデータを年間集計する』（右近）
以下のような表が同じシートにあります。
月別で4月から3月まであります。
ここに表示しているのは、同じ名前となっていますが、
月によって異なる名前が出てきます。必ずしも以下のような並びになるとは
限らないです。
これは、基シートが別にあって、以下はOFFSET関数のデータになります。
4月
A B C D E ・・・ 1 名前 日付 番号 日付 番号・・・ 2 鈴木太郎 4/ 1 100 4/ 9 115 3 山田次郎 4/ 4 105 4/12 119 4 小?蜑ﾔ子 4/10 108 4/16 120 5 山下琢夫 4/ 8 110 4/18 130 ・ ・ ・
5月
A B C D E ・・・ 1 名前 日付 番号 日付 番号・・・ 2 鈴木太郎 5/ 2 136 5/13 144 3 山田次郎 5/ 5 139 5/15 148 4 小?蜑ﾔ子 5/ 9 140 5/18 151 5 山下琢夫 5/10 141 5/26 153 ・ ・ ・
上記のデータを別シートに以下の形に横に並べたいです。
大体、月に日付と番号のセットが3〜5件ほどあります。
良い方法をご教示ください。よろしくお願いします。
年間合計
A B C D E F G H I・・・ 1 名前 日付 番号 日付 番号 日付 番号 日付 番号 2 鈴木太郎 4/ 1 100 4/ 9 115 5/ 2 136 5/13 144 3 山田次郎 4/ 4 105 4/12 119 5/ 5 139 5/15 148 4 小?蜑ﾔ子 4/10 108 4/16 120 5/ 9 140 5/18 151 5 山下琢夫 4/ 8 110 4/18 130 5/10 141 5/26 153 ・ ・ ・
＜ 使用 Excel：Excel2013、使用 OS：Windows7 ＞
To 右近 さん
>月別で4月から3月まであります。
・そのシート名は何ですか？12シートの名前、全てを教えてください。 ・シートは全部で12シートではなく、他にあるんですよね？ ・マクロを使用してもいいですか？ （マリオ） 2016/03/16(水) 13:11
（γ） 2016/03/16(水) 21:57
γさんとかぶりましたが、メモしましたので。
交通整理だけ。
>>いいえ、最後の集計以外は同一シートです。
同一シートなのに、4月分も5月分も行番号が 1行目からなんですか？？ それに、最後の集計シート以外はということですけど【基シート】もあるのでは？
>>基シートが別にあって、以下はOFFSET関数のデータになります。
その元シートデータってどんなもの（レイアウト）で、どこにあるのでしょうか？ で、これを OFFSET関数で月別表に展開しているんでしょうかね。 その数式をアップされてはいかがでしょう。
●もしかしたら、元シートから集計シートに展開できるかもとおもったりしているんですが。
（β） 2016/03/16(水) 22:05
4月が7行目から、5月は70行目から・・・以降3月まであります。
元データ、いわゆる直接手入力した同じシートにあって、今回何とか年間集計させたいオフセット関数を入れているのがBD7からに入っています。
BD7: =IFERROR(OFFSET(A6,AZ7,37),"")こんな感じです。
これを12ヶ月分のデータを以下のように集計させたいのです。
すみません。
A B C D E F G H I・・・ 1 名前 日付 番号 日付 番号 日付 番号 日付 番号 2 鈴木太郎 4/ 1 100 4/ 9 115 5/ 2 136 5/13 144 3 山田次郎 4/ 4 105 4/12 119 5/ 5 139 5/15 148 4 小?蜑ﾔ子 4/10 108 4/16 120 5/ 9 140 5/18 151 5 山下琢夫 4/ 8 110 4/18 130 5/10 141 5/26 153 ・ ・ ・ を
（右近） 2016/03/16(水) 22:25
最初に示した4月、5月、・・・の例示は、
いずれも1,2,3行があります。
これが同一シートにあるってどういうこと？
なにか さっぱりですな。
（γ） 2016/03/16(水) 22:45
よろしくお願いします。
（右近） 2016/03/16(水) 23:04
これも分かりやすいようにということで、
実際とは別の並び方を書いているのですか？
分かりやすいように、というのが、返って混乱を来す元になっていると思います。
省略せずに説明すれば、コメントがいただけるでしょう。
がんばって説明してください。私はここで。
（γ） 2016/03/16(水) 23:06
4月
D E F G H
6 名前 日付 番号 日付 番号
7 山田太郎 4/ 4 6 4/21 25
8 鈴木次郎 4/ 7 9 4/23 29
9 本田五郎 4/ 9 12 4/25 32
10 佐藤三郎 4/13 14 4/30 35
11 加藤四郎 4/20 20 4/30 40
･
･
5月
69 名前 日付 番号 日付 番号 日付 番号 70 加藤四郎 5/ 1 45 5/15 55 71 鈴木次郎 5/ 4 47 5/17 57 72 山田太郎 5/ 8 49 5/20 59 5/29 69 73 佐藤三郎 5/11 50 5/22 61 74 本田五郎 5/13 53 5/23 63 ･ ･
6月
132 名前 日付 番号 日付 番号 日付 番号
133 佐藤三郎 6/ 1 72 6/20 90
134 山田太郎 6/ 3 75 6/22 93 6/29 102
135 加藤四郎 6/ 9 78 6/25 95
136 鈴木次郎 6/13 82 6/27 96 6/30 105
137 本田五郎 6/18 86 6/28 99
･
･
上記の表をOFFSET関数等？を活用して、別のシートに以下のように集計させたいのです。
名前の順番は、基本的に4月の順番で、新規で追加されたら、その下に出てくるようになります。
出来れば、指定の条件で並べ替えさせられたら最高です。
例えば、五十音順だったり、地区別だったりです。
年間
D E F G H I J K L M N O P Q R
6 名前 日付 番号 日付 番号 日付 番号 日付 番号 日付 番号 日付 番号 日付 番号
7 山田太郎 4/ 4 6 4/21 25 5/ 8 49 5/20 59 5/29 69 6/ 1 72 6/20 90
8 鈴木次郎 4/ 7 9 4/23 29 5/ 4 47 5/17 57 6/ 3 75 6/22 93 6/29 102
9 本田五郎 4/ 9 12 4/25 32 5/13 53 5/23 63 6/ 9 78 6/25 95
10 佐藤三郎 4/13 14 4/30 35 5/11 50 5/22 61 6/13 82 6/27 96 6/30 105
11 加藤四郎 4/20 20 4/30 40 5/ 1 45 5/15 55 6/18 86 6/28 99
･
･
昨日、分かりづらい説明でご迷惑をおかけしました。
どうぞよろしくお願いします。
（右近） 2016/03/17(木) 18:04
失礼します。
わかりにくさのポイントは２つあります。
１．月ごとの領域
最初の説明では A列からでした。 次の説明では D列からでした。 で、次には
>>4月だと、C7からAU56まで >>5月だと、C70からAU119まで
ということで C列からになっています。 で、その次にアップされたサンプルでは また D列からになっています。
別のポイントで見ますと、
>>4月が7行目から、5月は70行目から・
つまり、月ごとのブロックがデータとして 63行、タイトル行もいれると 64行。 一方、
>>4月だと、C7からAU56まで >>5月だと、C70からAU119まで
ということですから、月ごとのブロックは 50行？（タイトル行をいれて 51行？）
★なぜ、そちらに、実際にあるシートレイアウトを説明されるのに、毎回、その場所が違っているのでしょうか？
２．元のデータ
これが使えるかどうかはわかりませんけど、年間集計をつくるうえで、むしろ、この元データを相手にしたほうが つくりやすいのでは？ というのが、βの推測です。（おそらく γさんも 同じ指摘をしておられると思います） なので、元データのレイアウト（場所とサンプル）を教えてくださいとお願いしています。
★説明いただいて、あぁ、これは使えないね となるかもしれませんが、だめもとで、元データ情報がどうなっているか 説明お願いします。
（β） 2016/03/17(木) 18:52
↑ あぁ、２つ、読み違えていたところがありました。
・行については、4月が タイトル行いれて 6行目から、5月がタイトル行入れて69行目から。 これは一貫していましたね。失礼しました。 ・もう１つ、元データといっておられるのは年間集計をしようとして OFFSET関数を使ったものをBD7あたりから作った。 でも、うまくいかないので、これは忘れて、あらためて、各月データから年間集計を作成したいと、そういうことですね。
★なので、開始列いがいは一貫していました。
（β） 2016/03/17(木) 18:58
ところで、トライされようとした式、BD7: =IFERROR(OFFSET(A6,AZ7,37),"")
これは、具体的に何をどうしようとされたのですか？ A6を起点に A6 が 0 として 下に AZ7に入っている値,右に37 移動したところの値ということですが。 具体的に、OFFSETで指し示しているセルはどこなのですか？
（β） 2016/03/17(木) 19:21
要件誤解している公算大ですが、とりあええず試してください。 ★印のところ、実際のシート名になおしてください。
Sub Test()
Dim j As Long
Dim x As Long
Dim i As Long
Dim line As Range
Dim body As Range
Dim k As Variant
Dim dic As Object
Dim y As Long
Dim w As Variant
Dim mx As Long
Application.ScreenUpdating = False
Set dic = CreateObject("Scripting.Dictionary")
With Sheets("Sheet1") '★元シート
For i = 7 To .Range("D" & Rows.Count).End(xlUp).Row Step 63
x = .Cells(i - 1, "D").End(xlToRight).Column
Set body = .Cells(i, "D").Resize(62, x - 3)
For Each line In body.Rows
k = line.Cells(1).Value
If k = "" Then Exit For
If Not dic.exists(k) Then
Set dic(k) = CreateObject("Scripting.Dictionary")
dic(k)(dic(k).Count) = k
End If
For j = 2 To line.Columns.Count Step 2
dic(k)(dic(k).Count) = line.Cells(j).Value
dic(k)(dic(k).Count) = line.Cells(j + 1).Value
Next
Next
Next
End With
With Sheets("Sheet2") '★転記シート
.UsedRange.ClearContents
y = 2 '転記開始行
For Each k In dic
w = dic(k).items
.Cells(y, "A").Resize(, UBound(w) + 1).Value = WorksheetFunction.Transpose(WorksheetFunction.Transpose(w))
y = y + 1
If UBound(w) > mx Then mx = UBound(w)
Next
.Range("A1").Value = "名前"
.Range("B1:C1").Value = Array("日付", "番号")
.Range("B1:C1").Copy .Range("B1").Resize(, mx)
.Select
End With
End Sub
（β） 2016/03/17(木) 20:23
またまた横から茶々入れ失礼します。
> まずどこからはいればよいのかから・・・すみません。 > 詳しく教えて頂けますか？
マクロを知らないのであれば、 基本的なテキストを買って勉強してください。 何から何まで人に聞いちゃダメですよ。 # 詳しく教えようと思うと一冊の本になってしまうはずです。 # マクロの動かしかたくらい勉強してくださいな。
βさんの提示されたマクロ利用で一件落着かもしれませんが、 「何でも関数でできませんか」と言う前に、 手作業でもいいから結果を出す努力をされたほうがいいんじゃないかと思う。
直前のスレッドで回答された式をあなたは理解できていますか？ ソートも式で、などとリクエストする前に、 ご自分でExcelに備わっているソート処理を実行することです。
さて、今回のテーマも、 ご自分でまず手作業で実行することにトライしたらどうですか？ 以下のような方針で実行してみてはどうですか？
(1)4月の欄を合計に使うんじゃなくて、
合計は合計として別の領域に作成しましょう。
4月の欄は、それはそれとして残すべきです。
(2)まず各月に登場するメンバの重複を除いたものを作成します。
それには、別のシートに氏名の列をいったんコピーして、
「重複の排除」機能を利用すれば可能です。
(3)各月のデータから、VLOOKUPで引っ張ってきます。
(4)問題は、"該当しない箇所は詰めて作成する"ことでしょうか？
それには、少しトリッキーですが、以下のような手があります。
=IFERROR(VLOOKUP(氏名のセル,月ごとの表,何番目かを表すセル,FALSE),NA())
のように、該当しない場合は、#N/Aを返すような式にしておきます。
(5)そして、表引きが終わったら、コピーで自分自身に値複写します。
（その前に、このあたりでいったん、シートごとバックアップをとったほうがよいかも）
「検索と選択」の「ジャンプ」機能で エラー値 だけを選択します。
その状態で、「セルの削除」「左方向にシフト」を実行すれば、
該当なしデータを削除できて、仕上がります。
(6) > 出来れば、指定の条件で並べ替えさせられたら最高です。
> 例えば、五十音順だったり、地区別だったりです。
地区って何って感じですが、これもソート機能を使うべきで、
関数でする話じゃないです。
（関数もマクロも）他人に丸投げするまえに、 手作業が入っても、ご自分でトライすることが必要だと思う。 すべてを関数式で書きたい、といったことは考えないほうがよい。 Excelに備わった機能を使いこなすことを考えたほうがよいです。 関数で簡単にできるならExcelが特別の機能を提供するはずがない。
もちろん、高度な関数式やマクロを勉強することも大切ですが、 両面から攻めていったほうが実りは多いはずです。
--------- なお、個人的には、単に各月のデータを横にならべるだけにして、詰めないほうが便利かと思う。 詰めてしまうことにより失われる情報もあることに気づくべきかもしれません。
その後にどんな使い方をするかに依存しますから、 そのこともよく検討すべきでしょう。
（γ） 2016/03/18(金) 07:16
あぁ、関数、あるいはエクセル標準機能による操作での解決を希望しておられたんですね。 詳しく という意味が、マクロを使えるようにするまでの環境づくりのことをいわれているのか そうではなく、アップされたコードの中身についていわれているのか、わかりませんが いずれであっても、もちろん、お手伝いはできます。
でも・・
それより、関数やエクセル標準機能による解決が 右近さんにとって有益なのではと思います。 で、その領域の場合、このレイアウトから要望されている年間の表に落とし込む関数、 βには荷が重すぎます。 （せめて、エクセル標準機能で、それらしいものができないかどうか、いろいろ試していますが）
専門家さんからの回答をお待ちください。
以下は余談です。
・列について、レイアウトがどうなっているのかの説明が、まだ、ないですね。
・このデータ、各人が入力しているのか、管理者が、伝票のようなものからまとめて入力しているのか そこは、わかりませんけど、月によって 横の項目数ってきまっているのかな？ それとも、月の最後に、あぁ、今月は一番多い人が10項目だったと、そういう感じで、管理者が 各月のタイトル行を10項目まで記入するのかな？
・月ごとに何人分と決めたレイアウト。もし、それ以上の人数になれば？ かりに、超難解な数式で、このデータを参照して年間表をつくることができたとして、人数がふえて 各月の行数を増やしたとき、その数式で、おそらく基準にしている要素が異なってくるので、もう、しっちゃか めっちゃかの数式変更が必要になりそう？
・たとえば、このデータが A,B,C ３列のみで、各データは、淡々と上からつめて追記していく。 こういうレイアウトなら、βにも、なんとか扱えるかもしれません。 もちろん、そのために入力がしづらくなる、入力チェックが煩雑になる ということなら本末転倒ですが もし、単純なレイアウトにしておければ、適宜、年月、名前で並び替えをしてやることで月別チェック、人別チェックも やりやすくなるかもしれませんし。
・そういった単純なレイアウトにしておいて、必要に応じて、今提示されている年間表、あるいは、別の見方のレポートが 必要になった際に、この単純なレイアウトから展開する。もしかしたら、ピボットや小計や統合 等々の機能で 簡単に作り上げることもできるかもしれません。
（β） 2016/03/18(金) 07:29
提案したように元データを
名前 日付 番号 の3列だけで、ずら〜っと入力する運用ができないとして、 現在のデータを別シートの A,B,C 列に 組み変えて配置できれば 以下の数式が使えるかと思います。
組替配置、なんとか、エクセル標準機能で簡単にできないかと試行錯誤しましたが、結局は
・値の貼り付け ・1〜5行削除、A〜C列削除 ・切り貼りで D列以降のものを、A,B,C列に 切り貼り ・名前と日付で並び替え ・B列空白セルをジャンプ機能で選択し ・選択されたセルの行全体を削除
こんな操作をしました。もっと簡単な方法もあるかもしれません。
で、ここから本番作業です。
１．A列を E列にコピペ ２．E列を重複の削除で一意化。 ３．F2 : =IFERROR(INDEX(OFFSET($B:$B,,MOD(COLUMN()-COLUMN($E2)-1,2)),SMALL(IF($A$2:$A$10000=$E2,ROW($A$2:$A$10000)),QUOTIENT(COLUMN()-COLUMN($E2)+1,2))),"") これを、Ctrl/Shift/Enter で入力。（式そのものはもっとスマートなものもあると思います） ４．F2 を ずるずる右にフィルコピーし、そのまま下にフィルコピー
なお、
・F,H,J,・・・等の書式は、あらかじめ 日付書式に ・1行目のタイトルは手入力
とりあえず、これで 提示の年間レイアウトができました。
（β） 2016/03/20(日) 09:00
追伸です。
↑の手順内で、E列の名前に一意化をエクセル機能で行いました。 これが一番いいと思っているのですが、仮にここも関数でということだと、ネットをさがすと 以下のようなものがありました。（とっても重いですが）
E2 : =A2
E3 : =IFERROR(VLOOKUP("*",IF(COUNTIF(E$2:E2,A$2:A$10000)=0,A$2:A$10000),1,FALSE),"")
これを Ctrl/Shift/Enter で入力し下にフィルコピー。
また、できあがるレイアウトは少し異なりますが、A,B,C列の元データを相手にピボットを使えば たてが人名、横が日付のマトリックスがいとも簡単に完成します。
なにがいいたいかというと、元データをシンプルな形で持っておけば、関数処理にしろエクセル機能による処理にしろ 簡単に、かつ柔軟に扱えるということです。 もちろん、マクロ処理をする場合も、コードがきわめてシンプル、かつ簡単なものになります。
是非、ご一考ください。
（β） 2016/03/20(日) 13:55
Excelの一般機能を使う方法 （データ表の日付はシリアル値、番号は数値である。と言う前提で）
１．月のタイトル行の、E6に以下の数式を入れ、右へフィルコピー
=CHOOSE(MOD(COLUMN(B1),2)+1,"日付/","番号/")&MONTH($E7)&"_"&INT(COLUMN(B1)/2)
E6をコピーし、E69,E132へコピーし、右へフィルコピー
（名前以外のタイトルが全ての月でユニークならなんでも可）
２．集計表を表示したいシートの起点セルを選択し、データタブの統合を選択
集計の方法：合計
統合元範囲：テキストボックスの右のボタンを押下、４月の表 D6:??を選択し、Enter、追加ボタン押下
統合の基準：上端行、左端列にチェックを入れ、ＯＫボタン押下
３．出力された集計表のセルA1に"名前"と入力 ４．集計表全体を選択し、Ctrl＋G同時押しで、ジャンプ機能を呼出し、セル選択ボタン押下、空白セルにチェック して、ＯＫ ５．空白セルが選択された状態で、右クリックショートカットメニューから、削除、左方向にシフトでＯＫ ６．各月データ表と集計表のタイトルを元の、日付、番号に戻す
（きまぐれ） 2016/03/21(月) 09:22
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.