はじめての質問でわかりづらいと思いますが、お願い致します。
Excel2003で建築部材の入出庫管理をしたいと考えていますが、日々の建築部材出入の記録用の入力シートにデータがあり、そこから貸出し状況シートへ(貸出に対してバラバラ返却の状況)を複数の条件にて重複したデータも抜出したい。 貸出し状況シートのABCDはなんとか抜出しましたが、EFGHIJ・・・が分かりませんのでご教授下さい。
入力シート
A B C D E F G ・・・・
1 部材番号 入出庫日 数量 貸出現場 出入処理
2 ABC100 9月10日 50 空陸 返却
2 DEF100 9月10日 20 空陸 返却
3 DEF100 9月6日 30 空陸 返却
3 DEF100 9月4日 50 空陸 返却
4 DEF100 9月2日 100 山川 貸出
5 DEF100 9月1日 100 空陸 貸出
6 ABC100 9月1日 100 空陸 貸出
・
・
貸出し状況シート
A B C D E F G H I J・・・・
1 貸出現場 部材番号 出庫日 数量 返却日1 数量1 返却日2 数量2 返却日3 数量3
2 山川 DEF100 9月2日 100
3 空陸 DEF100 9月1日 100 9月4日 50 9月6日 30 9月10日 20
4 空陸 ABC100 9月1日 100 9月10日 50
5
・
・
No.4
- 回答日時:
またまたお邪魔します。
補足のエラー部分をみると、数式が配列数式になっていないように思われます。
この画面から各セルにコピー&ペーストされているというコトですので、
セルに貼り付け後
(1)数式バー内で一度クリック
(2)F2キーを押す
(3)貼り付けセルでダブルクリック
上記(1)~(3)のどの操作でもかまいませんので行ってみてください。
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定!
数式が{ }マークで囲まれ、配列数式になります。
その後前回書いたようにセルの表示形式を「日付」・「標準」など設定しオートフィルでコピーしてみてください。
Sheet2のE2・F2セルは範囲指定し2列ずつ列方向にコピーは忘れないでください。
今こちらでExcelを開き、この画面からコピー&ペーストするとちゃんと表示できたみたいです。
今一度頑張ってトライしてみてくださいね。m(__)m
No.3ベストアンサー
- 回答日時:
何度もお邪魔します。
お礼欄を読ませてもらって・・・
関数での方法をご希望だというコトですので、無理やり関数でやってみました。
↓の画像(小さくて見にくいかもしれません)のようにやはりSheet1に作業用の列を設ける方法しか思い浮かびませんでした。
Sheet1の作業列F2セルを
=IF(E2="返却",$D2&"_"&$A2,"")
としてオートフィルでずぃ~~~!っと下へコピー!
そしてSheet2のA2セル(配列数式になります)に
=IF(COUNTIF(Sheet1!$E:$E,"貸出")<ROW(A1),"",INDEX(Sheet1!$A$1:$E$1000,SMALL(IF(Sheet1!$E$1:$E$1000="貸出",ROW($A$1:$A$1000)),ROW(A1)),MATCH(A$1,Sheet1!$A$1:$E$1,0)))
としてD2セルまでコピー!
※ C2セルはエラーになりますので、C列のみ別途数式を入れます。
C2セル(これも配列数式です)は
=IF(A2="","",INDEX(Sheet1!$B$1:$B$1000,SMALL(IF(Sheet1!$E$1:$E$1000="貸出",ROW($A$1:$A$1000)),ROW(A1))))
としてセルの表示形式は「日付」にしておきます。
A2~D2セルを範囲指定 → D2セルのフィルハンドルで下へコピー!
E2セル(配列数式・セルの表示形式は「日付」)は
=IF(COUNTIF(Sheet1!$F:$F,$A2&"_"&$B2)<COLUMN(B1)/2,"",SMALL(IF(Sheet1!$F$1:$F$1000=$A2&"_"&$B2,Sheet1!$B$1:$B$1000),COLUMN(B1)/2))
F2セル(配列数式ではありません。表示形式は「標準」)は
=IF(E2="","",SUMPRODUCT((Sheet1!$D$1:$D$1000=$A2)*(Sheet1!$A$1:$A$1000=$B2)*(Sheet1!$B$1:$B$1000=E2),Sheet1!$C$1:$C$1000))
という数式を入れ、E2・F2セルを範囲指定 → F2セルのフィルハンドルで2列ずつ列方向にコピー!
そのまま下へオートフィルでコピーすると画像のような感じになります。
※ 配列数式の場合はPCに負担を掛けますので、データ量が多い場合は作業列を多くしてでも
極力配列数式にしない方が良いと思います。
この程度で参考になりますかね?m(_ _)m
この回答への補足
毎度お世話になります。
手抜きでそのままコピペで関数と配列式を移して実行してみましたが、Sheet2のA2-D2セルのみデータの表示が出ましたが貸出のデータではなさそうでした。
配列関数もまだ初心者で修正箇所がわかりませんでした。
ご教授頂けないでしょうか。
宜しく、お願い致します。
貸出現場部材番号入出庫日数量返却日1数量1
空陸ABC1002011年9月10日50
#NUM!#NUM!#NUM!#NUM!
#NUM!#NUM!#NUM!#NUM!
大変早い指摘をいただき心からお礼いたします。
ご指摘どおり、配列関数が実行になっていなかったり、数量のタイトルが違っていたり、操作列1のセル文字接続の記号が"_"の指示で"-"になっていたりしていました。
1時間かかりましたが完璧でした。(ドジでした。)
これを元に発展させてみます。
ありがとうございました。
No.2
- 回答日時:
No.1です!
たびたびごめんなさい。
投稿後に思ったのですが・・・
重要なのは貸出した部材がいくつ返却されていないのか?ということだと思います。
余計なお世話かもしれませんが、そちら重視で考えてみました。
貸出列・返却列は別列の方が良いと思いますので、勝手に↓のような感じの表にしてみました。
(日付は無視しています)
Sheet1に作業用の列を2列設けています。
作業列1のF2セルに
=IF(A2="","",A2&"-"&B2)
作業列2のG2セルに
=IF(A2="","",IF(COUNTIF($F$2:F2,F2)=1,ROW(),""))
という数式を入れ、F2・G2セルを範囲指定 → G2セルのフィルハンドルでずぃ~~~!っと下へコピーしておきます。
そして、Sheet2のA2セルに
=IF(COUNT(Sheet1!$G:$G)<ROW(A1),"",INDEX(Sheet1!A:A,SMALL(Sheet1!$G:$G,ROW(A1))))
という数式を入れ隣のB2セルまでコピー
C2セルを
=IF(A2="","",SUMIF(Sheet1!$F:$F,$A2&"-"&$B2,Sheet1!D:D))
として、隣のD2セルまでコピー
E2セルは単に
=IF(A2="","",C2-D2)
最後にSheet2のA2~E2セルを範囲指定し、E2セルのフィルハンドルで下へコピーすると
画像のような感じになります。
これでどの「部材番号」がどこの「貸出現場」にいくつ残っているかすぐに判断できると思います。
※ 最初に書いたようにご希望の方法でなかったら読み流してくださいね。m(_ _)m
配列数式での計算が必要になり必要に駆られての質問でした。 継続して返却日付のための表示数式を模索していますが、作業列を省いた配列数式にこだわっていましたが、わかりやすくしたほうが早く式の完成に近くなることを教わりました。 教えていただいたところは完璧でした。
ありがとうございます。
No.1
- 回答日時:
こんばんは!
途中まではできているというコトなので、余計なお世話になるかもしれませんが・・・
VBAで無理やりやってみました。
Sheet1にデータがありSheet2に表示するとします。
Sheet3を作業用のSheetとして使用していますので、Sheet3は使っていないという前提です。
画面左下にあるSheet1のSheet見出し上で右クリック → コードの表示 → VBE画面がでますので
↓のコードをコピー&ペーストしてマクロを実行してみてください。
(Alt+F8キー → マクロ → マクロ実行です)
Sub test() 'この行から
Dim i, j As Long
Dim ws2, ws3 As Worksheet
Set ws2 = Worksheets(2)
Set ws3 = Worksheets(3)
ws2.Cells.ClearContents
ws3.Cells.Clear
Application.ScreenUpdating = False
With ws2.Cells(1, 1)
.Value = Cells(1, 4)
.Offset(, 1) = Cells(1, 1)
.Offset(, 2) = "出庫日"
.Offset(, 3) = Cells(1, 3)
End With
i = Cells(Rows.Count, 1).End(xlUp).Row
Range(Cells(2, 1), Cells(i, 5)).Copy Destination:=ws3.Cells(1, 2)
For j = 1 To ws3.Cells(Rows.Count, 2).End(xlUp).Row
ws3.Cells(j, 1) = ws3.Cells(j, 5) & ws3.Cells(j, 2)
Next j
For j = 1 To ws3.Cells(Rows.Count, 1).End(xlUp).Row
If ws3.Cells(j, 6) = "貸出" Then
With ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Value = ws3.Cells(j, 5)
.Offset(, 1) = ws3.Cells(j, 2)
With .Offset(, 2)
.Value = ws3.Cells(j, 3)
.NumberFormatLocal = "m月d日"
End With
.Offset(, 3) = ws3.Cells(j, 4)
End With
End If
Next j
ws3.Columns("A:F").Sort key1:=ws3.Cells(1, 3), order1:=xlAscending
For i = 2 To ws2.Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To ws3.Cells(Rows.Count, 1).End(xlUp).Row
If ws2.Cells(i, 1) & ws2.Cells(i, 2) = ws3.Cells(j, 1) And ws3.Cells(j, 6) = "返却" Then
With ws2.Cells(i, Columns.Count).End(xlToLeft).Offset(, 1)
.Value = ws3.Cells(j, 3)
.NumberFormatLocal = "m月d日"
.Offset(, 1) = ws3.Cells(j, 4)
End With
End If
Next j
Next i
j = ws2.UsedRange.Columns.Count
For i = 5 To j Step 2
With ws2.Cells(1, i)
.Value = "返却日" & (i - 3) / 2
.Offset(, 1) = "数量" & (i - 3) / 2
End With
Next i
ws2.Columns.AutoFit
ws3.Cells.Clear
Application.ScreenUpdating = True
End Sub 'この行まで
※ 一旦マクロを実行すると元に戻せませんので、別BookにSheet1のデータをコピーしてマクロを試してみてください。
参考になれば良いのですが・・・m(_ _)m
お礼とお詫び
ご丁寧にご教授頂き大変恐縮ですが、エクセルは初心者ですので簡単な関数や配列数式がやっとでした。
ご教授頂いたマクロを使えるように努力します。
こちらの質問の仕方が悪く大変申し訳ございません。
今回がはじめての質問でしたので質問内容に今後教えていただく内容を具体的に記述致します。
どうか、お気を悪くされないでください。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルの数式で教えてください。 2 2023/03/10 08:51
- 金銭トラブル・債権回収 5月27日 グッズ代 6300円 8月6日 夜 2万円 貸している 22日 2万円 貸してあげた 1 3 2022/11/20 20:23
- その他(暮らし・生活・行事) 蔵書検索で閉館中の図書館に返却をしましたが… 6 2023/04/14 20:49
- 九州・沖縄 沖縄2泊3日のスケジュール 4 2023/03/12 10:58
- Excel(エクセル) エクセル関数について 2 2022/04/13 18:25
- Visual Basic(VBA) 列と行の名前(重複あり)が交差するセルに、データを入力したい 3 2022/06/12 11:17
- その他(データベース) c言語の問題です。これを踏まえてコーディングしたいのでおしえていただきたいです。 3 2023/08/03 09:27
- 九州・沖縄 沖縄・那覇でのレンタカー返却 4 2023/04/26 14:10
- 妊娠 7月30日と9月10日に性行為をしました。 7月30日は避妊はしておらず9月10日はコンドームをつけ 1 2022/09/25 08:50
- 避妊 7月30日と9月10日に性行為をしました。 7月30日は避妊はしておらず9月10日はコンドームをつけ 1 2022/09/25 08:20
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
Excel 日付を比較したら、同じ...
-
Outlookを立ち上げたらGoogleロ...
-
outlookのメールが固まってしま...
-
会社PCのメールが更新されない
-
【スプレッドシート】指定の日...
-
ウィンドウィズ メモ帳で日付だ...
-
英数字のみ全角から半角に変換
-
Excelで空白以外の値がある列の...
-
Microsoft Formsの「個人情報や...
-
microsoft office
-
MicrosoftOfficeについて質問で...
-
マイクロソフト 一時使用コード...
-
outlookで宛先が異なるメールを...
-
Outlookでの時間指定送信機能に...
-
【スプレドシート】目標達成の...
-
自分の専門分野の仕事。初見で...
-
Microsoft Officeを2台目のPCに...
-
【Excel VBA】PDFを作成して,...
-
エクセルでXLOOKUP関数...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Office 2021 Professional Plus...
-
エクセルで例えば、関数を使っ...
-
Microsoft Formsの「個人情報や...
-
1つのPCに「Excel 2010」「Exc...
-
エクセルで例えば、A1に㈱ベ...
-
理由を教えてください。
-
エクセルでXLOOKUP関数...
-
マイクロソフト オフィスについて
-
VLOOKUP関数について
-
teams設定教えて下さい。 ①ビデ...
-
Googleのスプレッドシートでシ...
-
【Excel VBA】PDFを作成して,...
-
Microsoft365で写真をアルバム...
-
Outlook で宛先が複数の場合の人数
-
Excel テーブル内の空白行の削除
おすすめ情報