[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『データの存在する列見出しの表示』(鼓動)
備品の貸出管理のファイルを作成しているのですが行き詰っています。
アドバイスお願いします
まずシート1に下記のような各備品に対する貸出履歴を集計します。
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
A B C D E F G H I J K 1 備品名 初期在庫 8/15返却 8/16返却 8/17返却 返却計 8/25貸出 8/27貸出 8/30貸出 貸出計 在庫計 2 aa 0 3 1 4 1 1 2 2 3 bb 0 1 1 2 1 1 2 0 4 cc 0 1 1 1 2 0 5 ↓続く
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
F列の返却計にはB2+SUM(C2:E2),J列にはSUM(G2:I2),K列にはF2-J2の計算式が入り、当然返却、貸出があれば列を挿入し返却日、貸出日を
追加します。
そしてシート2には備品ごとの返却、貸出履歴を表示したいです。
表示のさせ方ですが、履歴を見たい備品をフィルタで引っ張ると、
返却された日付や貸し出した日付が表示されるように
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
備品名 返却履歴 貸出履歴 備品名 返却履歴 貸出履歴
aa aa 8/15返却 8/25貸出
bb → 8/17返却 8/27貸出
cc
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
ここの掲示板内に似たような質問があったので([[20160116145411]])、これを参考に自分でもやってみたのですが、列見出しが文字列であると
いうことと列見出しの表示方法が違っていたため自分ではうまいこと表示できませんでした。
また、([[20040822023922]])も見たのですが、これだと複数データが存在する場合には使えませんでした(自分の能力不足かもしれませんが)
< 使用 Excel:Excel2010、使用 OS:Windows7 >
(1)備品名
(2)aa______8/15返却 8/17返却
(3)aa______8/25貸出 8/27貸出
(4)bb______8/16返却 8/17返却
(5)bb______8/25貸出 8/27貸出
(6)cc______8/17返却
(7)cc______8/25貸出
こんな感じで備品名の行を2行ずつにして、返却日と貸出日を別々の行に抽出する方法はどうでしょうか?
以下の式はシート2に入れます。
A2(下までコピペ)
=OFFSET(シート1!$A$2,D2,)
B2(下までコピペ)
=IF(ISEVEN(ROW()),"返却","貸出")
C2※配列数式として入力(下までコピペ)
=SUM((RIGHT(シート1!$A$1:$Z$1,2)=B2)*(OFFSET(シート1!$A$2:$Z$2,D2,)>=1))
D2(下までコピペ)
=ROW()/2-1
E2※配列数式として入力
=IFERROR(INDEX(シート1!$1:$1,,LARGE((OFFSET(シート1!$A$2:$Z$2,$D2,)>=1)*(RIGHT(シート1!$A$1:$Z$1,2)=$B2)*COLUMN(シート1!$A$1:$Z$1),$C2-COLUMN()+5)),"")
E2に入力した配列数式を、右と下にコピペすれば完成です。
(カイル) 2016/08/20(土) 10:59
上記の方法で実際やってみました結果、無事表示されました。
ですが、上司の要望は、シート2上で、貸出履歴を調べたい備品に対して、
貸出履歴が横に伸びていくのではなく縦に履歴が並ぶようにしてほしいということなので
少し理想と異なってしまいます。
オートフィルターで備品名で引っ張った際に履歴が縦に並んでいく形にはできないでしょうか?
また、配列数式の特徴なのかもしれませんが、列を挿入すると表示が消えてしまうのですが、
もし、シート2で備品名の左側にに1列挿入した場合には配列数式のどの辺を変えればよろしいでしょうか?
(鼓動) 2016/08/21(日) 17:16
こんばんわ。
今の列を挿入したりする元シートのレイアウトは式を複雑にするので、 一般的によくある家計簿や出納帳みたいに備品名・日付・返却・貸出のようにして、下にデータを追加するように出来れば一番良いんですけどねぇ。 管理もしやすくなるし、見やすさは人の好みですが慣れたら気にならないと思うんですけどね。
今のレイアウトを変えれないなら、一番初めのレイアウトで、シート1に作業列を追加して求める案です。 列の挿入には自動で対応します。
シート1の作業列 M2 =1 M3 =MAX(COUNT(C$2:INDEX(2:2,MATCH("返却計",$1:$1,0)-1)),COUNT(INDEX($2:$2,MATCH("返却計",$1:$1,0)+1):INDEX(2:2,MATCH("貸出計",$1:$1,0)-1)))+1 N1 ="Sheet1!C"&ROW()&":"&CHAR(MATCH("返却計",$1:$1,0)+63)&ROW() O1 ="Sheet1!"&CHAR(MATCH("返却計",$1:$1,0)+65)&ROW()&":"&CHAR(MATCH("貸出計",$1:$1,0)+63)&ROW() それぞれ下にオートフィル P1 ="A1:"&CHAR(MATCH("返却計",1:1,0)+61)&ROW() Q1 ="A1:"&CHAR(MATCH("貸出計",1:1,0)-MATCH("返却計",1:1,0)+63)&ROW()
シート2 A2 =INDEX(Sheet1!A$1:A$10,INDEX(ROW(Sheet1!A$1:A$10),MATCH(ROW(Sheet1!A1),Sheet1!M$1:M$10)))&"" B2 =IFERROR(INDEX(INDIRECT(Sheet1!N$1),AGGREGATE(15,6,1/(INDIRECT(INDEX(Sheet1!N:N,MATCH(A2,Sheet1!A:A,0)))>0)*COLUMN(INDIRECT(Sheet1!P$1)),COUNTIF(A$2:A2,A2))),"") C2 =IFERROR(INDEX(INDIRECT(Sheet1!O$1),AGGREGATE(15,6,1/(INDIRECT(INDEX(Sheet1!O:O,MATCH(A2,Sheet1!A:A,0)))>0)*COLUMN(INDIRECT(Sheet1!Q$1)),COUNTIF(A$2:A2,A2))),"") それぞれ下にオートフィル
(sy) 2016/08/21(日) 20:01
説明が難しいのですが
Sheet1のM列には1、3、4、6、7、9、9、11、12、13、14、15と続き、N列にはSheet1!C2:E2、Sheet1!C3:E3
・・・、O列にはSheet1!G2:I2、Sheet1!G3:I3…と表示され、
Sheet2のA列にはAAA、AAA、BBB、CCC、CCC、DDD…、B列、C列には何も表示されていません。
(鼓動) 2016/08/21(日) 23:17
こんにちわ。
今会社なので帰ってからじゃないとファイル見れないですけど、 気になったのが、M列は式は3行目から、M2セルは1を入力するだけ、 NとO列は1行目から式になりますけど、 P1とQ1の式も1行目でないといけません。 そこは大丈夫ですか?
後元データのシート名はSheet1で認識するようになってます。 シート名が変われば認識しません。 シート名が違うなら、全ての式のシート名も変更して下さい。
(sy) 2016/08/22(月) 11:18
割と長い期間、貸出管理を利用するので長い日数でも表示できるになってほしいのですが
そこは修正可能でしょうか?
また、要望ばっかで大変申し訳ないのですが、
シート2で履歴を表示した際に貸し出した点数、返却された点数を追記することは可能でしょうか?
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
備品名 返却履歴 返却点数 貸出履歴 貸出点数
aa 8/15返却 3 8/25貸出 1
8/17返却 1 8/27貸出 1
−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−
宜しくお願いいたします
(鼓動) 2016/08/22(月) 22:21
こんばんわ。
すいません。 AA列まで伸びた時の事を失念してました。
Sheet1の式を以下に変更して下さい。 M列は変更なし。 N1 ="Sheet1!C"&ROW()&":"&ADDRESS(ROW(),MATCH("返却計",$1:$1,0)-1,4) O1 ="Sheet1!"&ADDRESS(ROW(),MATCH("返却計",$1:$1,0)+1,4)&":"&ADDRESS(ROW(),MATCH("貸出計",$1:$1,0)-1,4) それぞれ下にオートフィル
P1 ="A1:"&ADDRESS(ROW(),MATCH("返却計",1:1,0)-3,4) Q1 ="A1:"&ADDRESS(ROW(),MATCH("貸出計",1:1,0)-MATCH("返却計",1:1,0)-1,4)
結果シートは、以下の並びとして、 備品名 返却履歴 返却点数 貸出履歴 貸出点数
A、B列は変更なし。 C2 =IFERROR(INDEX(INDIRECT(INDEX(Sheet1!N:N,MATCH(A2,Sheet1!A:A,0))),AGGREGATE(15,6,1/(INDIRECT(INDEX(Sheet1!N:N,MATCH(A2,Sheet1!A:A,0)))>0)*COLUMN(INDIRECT(Sheet1!P$1)),COUNTIF(A$2:A2,A2))),"") D2 =IFERROR(INDEX(INDIRECT(Sheet1!O$1),AGGREGATE(15,6,1/(INDIRECT(INDEX(Sheet1!O:O,MATCH(A2,Sheet1!A:A,0)))>0)*COLUMN(INDIRECT(Sheet1!Q$1)),COUNTIF(A$2:A2,A2))),"") E2 =IFERROR(INDEX(INDIRECT(INDEX(Sheet1!O:O,MATCH(A2,Sheet1!A:A,0))),AGGREGATE(15,6,1/(INDIRECT(INDEX(Sheet1!O:O,MATCH(A2,Sheet1!A:A,0)))>0)*COLUMN(INDIRECT(Sheet1!Q$1)),COUNTIF(A$2:A2,A2))),"") それぞれ下にオートフィル
(sy) 2016/08/22(月) 23:30
貸し出し先の部署ごとの貸出計を複数作ることは可能でしょうか
携帯からなので詳しく説明できないのですが‥
(鼓動) 2016/08/23(火) 15:46
こんばんわ。
>貸し出し先の部署ごとの貸出計を複数作ることは可能でしょうか
よく意味が分かりません?
シート1の貸出計が部署ごとに複数あると言う事ですか? 今のレイアウトでは返却計と貸出計は1つづつしかないけど、それがそれぞれ複数になると言う事ですか?
こう言う事?
N1 ="Sheet1!"&ADDRESS(ROW(),MATCH("*返却",$1:$1,0),4)&":"&ADDRESS(ROW(),MATCH("返却計*",$1:$1,0)-1,4) O1 ="Sheet1!"&ADDRESS(ROW(),MATCH("*貸出",$1:$1,0),4)&":"&ADDRESS(ROW(),MATCH("貸出計*",$1:$1,0)-1,4)
(sy) 2016/08/23(火) 20:54
返却計に部署ごとの区別はありません
各貸出計の前の列には当初の例の通り、日付+●●貸出といった形で貸出点数が表示され、列の挿入によって日付の追加もあります。
部署の追加にも対応できるとうれしいです
シート2の表示方法ですが、上の例のように各部署に貸し出した日付が部署Akから部署Cまで日付の若い順とは限らないので、 履歴として表示される際は、8/4B貸出の次に8/5A貸出、最後に8/6C貸出といった感じで日付順に縦に並んでほしいです
備品名 返却履歴 返却点数 貸出履歴 貸出点数
aa 8/15返却 3 8/4B貸出 1 8/17返却 1 8/5A貸出 1 8/18返却 1 8/6C貸出 1
宜しくお願い致します
(鼓動) 2016/08/23(火) 23:31
もう関数でやる要件では無いですね。
マクロはダメなんですか?
(sy) 2016/08/24(水) 06:38
後日付で昇順に並べ替えるなら、今は8/5A貸出と月日しか無いので 12/28A貸出と1/6A貸出のように年を跨がると並べ替えは出来ません。
また日付部分だけを抜き出して判定しないと行けないので 8/4と8/15では文字数が違うので、日付の抜き出しだけでも かなり複雑な数式になってしまいます。
上記2つはマクロでも問題になります。
20160804A貸出のような年月日まで記載して文字数を統一した名前にしなければ、 並べ替えは出来ません。
(sy) 2016/08/24(水) 08:02
出きるか出来ないかと言う話でしたら、 作業列を増やしてもう少し複雑な数式にすれば関数でも出来ます。
ただ今の項目名だと年を跨がった時に正しく並べ替えはされません。
実務で使うならメンテナンス性なども考慮したら関数は止めた方が良いです。 私個人的には最後の要件では関数の中身を理解するより、マクロを勉強する方が簡単と思います。
(sy) 2016/08/24(水) 10:15
>ちなみにですが8/23 20:54 に提示していただいた数式を使った場合、 複数の貸出計には対応できるが、並び替えまで対応するには難しいということでしょうか
そう言う事ではありません。 年を判断する情報が無いと言う事です。 1/4とだけ書いてあってもPCは2016年の1/4なのか、2017年の1/4なのかを判断出来ないと言う事です。 これは関数は関係ありません。 如何なる方法を持ってしても不可能です。
(sy) 2016/08/24(水) 12:51
こんにちわ。
暫く忙しくて放置になっていたので、もう見られてないかも知れませんが、 結果が1000行超えれば重いので、お勧めはしませんが一応関数で最後の要件を作ってみました。
作業列は全て結果シートの方だけにしました。
A2 =INDEX(G$1:G$100,MATCH(MAX(INDEX((I$2:I$100<=ROW())*I$2:I$100,0)),I$1:I$100,0))&"" B2 =IFERROR(INDEX(M$1:M$100,MATCH($A2&COUNTIF($A$2:$A2,$A2),INDEX($J$1:$J$100&$L$1:$L$100,0),0)),"") C2 =IFERROR(INDEX(N$1:N$100,MATCH($A2&COUNTIF($A$2:$A2,$A2),INDEX($J$1:$J$100&$L$1:$L$100,0),0)),"") D2 =IFERROR(INDEX(U$1:U$100,MATCH($A2&COUNTIF($A$2:$A2,$A2),INDEX($R$1:$R$100&$T$1:$T$100,0),0)),"") E2 =IFERROR(INDEX(V$1:V$100,MATCH($A2&COUNTIF($A$2:$A2,$A2),INDEX($R$1:$R$100&$T$1:$T$100,0),0)),"")
G2 =Sheet1!A2&"" H2 =IF(G2="",0,MAX(COUNTIFS(J$2:J$1000,G2,K$2:K$1000,">0"),COUNTIFS(R$2:R$1000,G2,S$2:S$1000,">0"))) I2 2 I3 =IFERROR(I2+H2,"") J2 =IF(ROW()>COUNTA(Sheet1!A$2:A$100)*COUNTIF(Sheet1!$1:$1,"*返却")+1,"",INDEX(Sheet1!$A$2:$A$100,INT((ROW()-2)/COUNTIF(Sheet1!$1:$1,"*返却")+1))) K2 =IF(N2="","",MATCH(J2,Sheet1!A$2:A$100,0)*100000+INDEX(Q$1:Q$1000,MATCH(M2,P$1:P$1000,0))) L2 =IF(K2="","",COUNTIFS(J$2:J2,J2,K$2:K2,">0")) M2 =IF(J2="","",INDEX(O$2:O$1000,MOD(ROW()-2,SUMPRODUCT((O$2:O$1000<>"")*1))+1)) N2 =IFERROR((INDEX(Sheet1!$2:$100,MATCH(J2,Sheet1!A$2:A$100,0),MATCH(M2,Sheet1!$1:$1,0))&"")*1,"") O2 =IFERROR(INDEX(P$1:P$1000,MATCH(SMALL(Q$1:Q$1000,ROW(A1)),Q$1:Q$1000,0)),"") P1 =IF(RIGHT(INDEX(Sheet1!$1:$1,ROW(A1)),2)="返却",INDEX(Sheet1!$1:$1,ROW(A1)),"") Q1 =IF(P1="","",LEFT(P1,FIND("/",P1)-1)*1000+IFERROR(MID(P1,FIND("/",P1)+1,2)*10,MID(P1,FIND("/",P1)+1,1)*10)+ROW()) R2 =IF(ROW()>COUNTA(Sheet1!A$2:A$100)*COUNTIF(Sheet1!$1:$1,"*貸出")+1,"",INDEX(Sheet1!$A$2:$A$100,INT((ROW()-2)/COUNTIF(Sheet1!$1:$1,"*貸出")+1))) S2 =IF(V2="","",MATCH(R2,Sheet1!A$2:A$100,0)*100000+INDEX(Y$1:Y$1000,MATCH(U2,X$1:X$1000,0))) T2 =IF(S2="","",COUNTIFS(R$2:R2,R2,S$2:S2,">0")) U2 =IF(R2="","",INDEX(W$2:W$1000,MOD(ROW()-2,SUMPRODUCT((W$2:W$1000<>"")*1))+1)) V2 =IFERROR((INDEX(Sheet1!$2:$100,MATCH(R2,Sheet1!A$2:A$100,0),MATCH(U2,Sheet1!$1:$1,0))&"")*1,"") W2 =IFERROR(INDEX(X$1:X$1000,MATCH(SMALL(Y$1:Y$1000,ROW(A1)),Y$1:Y$1000,0)),"") X1 =IF(RIGHT(INDEX(Sheet1!$1:$1,ROW(A1)),2)="貸出",INDEX(Sheet1!$1:$1,ROW(A1)),"") Y1 =IF(X1="","",LEFT(X1,FIND("/",X1)-1)*1000+IFERROR(MID(X1,FIND("/",X1)+1,2)*10,MID(X1,FIND("/",X1)+1,1)*10)+ROW()) 下にオートフィル
1行目から式の列もあるので注意して下さい。
最後にマクロでも書いてみました。
Sub test() Const sh1n As String = "Sheet1" '入力シートのシート名 Const sh2n As String = "Sheet2" '結果シートのシート名 Dim sh1 As Worksheet Dim sh2 As Worksheet Dim c As Range Dim s As String Dim num1 As Integer Dim num2 As Integer Dim i As Long Dim j As Integer Dim cnt As Long Dim r Dim v1(1 To 100000, 1 To 4) Dim v2(1 To 100000, 1 To 4)
'変数に格納 Set sh1 = Sheets(sh1n) Set sh2 = Sheets(sh2n) r = sh1.Range("A1").CurrentRegion.Value
'返却情報 cnt = 0 num1 = WorksheetFunction.Match("*返却", sh1.Rows(1), 0) num2 = WorksheetFunction.Match("返却計", sh1.Rows(1)) - 1 For i = 2 To UBound(r, 1) For j = num1 To num2 If Right(r(1, j), 2) = "返却" Then If r(i, j) > 0 Then cnt = cnt + 1 v1(cnt, 1) = r(i, 1) v1(cnt, 2) = r(1, j) v1(cnt, 3) = r(i, j) v1(cnt, 4) = Left(r(1, j), InStr(r(1, j), "/") - 1) * 100 s = Mid(r(1, j), InStr(r(1, j), "/") + 1, 2) If IsNumeric(s) Then v1(cnt, 4) = v1(cnt, 4) + s Else v1(cnt, 4) = v1(cnt, 4) + Left(s, 1) End If End If End If Next j Next i
'貸出情報 cnt = 0 num1 = WorksheetFunction.Match("*貸出", sh1.Rows(1), 0) num2 = WorksheetFunction.Match("貸出計", sh1.Rows(1)) - 1 For i = 2 To UBound(r, 1) For j = num1 To num2 If Right(r(1, j), 2) = "貸出" Then If r(i, j) > 0 Then cnt = cnt + 1 v2(cnt, 1) = r(i, 1) v2(cnt, 2) = r(1, j) v2(cnt, 3) = r(i, j) v2(cnt, 4) = Left(r(1, j), InStr(r(1, j), "/") - 1) * 100 s = Mid(r(1, j), InStr(r(1, j), "/") + 1, 2) If IsNumeric(s) Then v2(cnt, 4) = v2(cnt, 4) + s Else v2(cnt, 4) = v2(cnt, 4) + Left(s, 1) End If End If End If Next j Next i
'データ転記 Application.ScreenUpdating = False sh2.Range("F:P").Insert sh2.Range("G1:J100000").Value = v1 sh2.Range("L1:O100000").Value = v2
'返却日順にソート With sh2.Sort.SortFields .Clear .Add Key:=Range("G1") .Add Key:=Range("J1") .Add Key:=Range("H1") End With With sh2.Sort .SetRange sh2.Range("G1").CurrentRegion .Header = xlNo .Apply End With
'貸出日順にソート With sh2.Sort.SortFields .Clear .Add Key:=Range("L1") .Add Key:=Range("O1") .Add Key:=Range("M1") End With With sh2.Sort .SetRange sh2.Range("L1").CurrentRegion .Header = xlNo .Apply End With
'書出し sh2.Range("A2:E" & Rows.Count).ClearContents For i = 2 To UBound(r, 1) '個数・書出し位置 Set c = sh2.Range("A" & Rows.Count).End(xlUp).Offset(1) num1 = WorksheetFunction.CountIf(sh2.Range("G:G"), r(i, 1)) num2 = WorksheetFunction.CountIf(sh2.Range("L:L"), r(i, 1)) '返却書出し If num1 Then cnt = WorksheetFunction.Match(r(i, 1), sh2.Range("G:G"), 0) sh2.Cells(cnt, "H").Resize(num1, 2).Copy c.Offset(, 1) End If '貸出書出し If num2 Then cnt = WorksheetFunction.Match(r(i, 1), sh2.Range("L:L"), 0) sh2.Cells(cnt, "M").Resize(num2, 2).Copy c.Offset(, 3) End If '備品名書出し If num1 >= num2 Then cnt = num1 Else cnt = num2 End If If cnt Then c.Resize(cnt).Value = r(i, 1)
Next i
'作業列削除 sh2.Range("F:P").Delete Application.ScreenUpdating = True
End Sub
(sy) 2016/08/27(土) 11:41
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.