人に聞けない痔の悩み、これでスッキリ >>

エクセルで日報を作成しています。
1sheetに365日分のセルを作成。
A1のセルに3月と入力すると以下の365行が「3月1日~3月31日」の
31行分の1か月分が表示になるようにするにはどんなテクニックが必要でしょうか?エクセルを終了して、その条件はキープしていて欲しい。

《元データ》
 A列  B列  C列  D列
1    月 
2
3 月日 曜日  本日の容量  残容量  合計
4 1月1日 月   150      20   170
5 1月2日 火   150      20   170
6 1月3日 水   200      20   220
7
:
368 12月31日........

《入力結果》
 A列  B列  C列  D列
1 3   月 ←このつきの前に「表示月」を入力 例えば「3」
2
3 月日 曜日  本日の容量  残容量  合計
4 3月1日 木         
5 3月2日 金   
6 3月3日 土   

34 3月31日土

が表示される。
 

このQ&Aに関連する最新のQ&A

A 回答 (8件)

No.2です。



> タイトル行に当たる「月日,曜日,本日の容量,残容量,合計」が
> 消えてしまいます。

あれおかしいですね。タイトル行は質問文にあるとおり3行目から始まってますか?

> やはり「オートフィルター」のマークがB1にも入ってしまうものなのですよね?

これは仕方がないです。A列だけなら▼マークを非表示にできるのですが、他の列はどうしてもマークが出てしまいます。

それが出るのが嫌なのでオートフィルタを使いたくないのであれば、アプローチを変えて、オートフィルタを使わずに、指定した月以外は非表示にするマクロにしてみました。No.2の回答の手順でマクロの画面を開き、元のマクロは消して以下をコピーして貼り付けてみてください。

No.2の回答と同様に、A1に月の数字を入力すると、その月だけの表示になります。A1を空白にするか、0などの数値を入力すると1年分の表示に戻ります。

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address <> "$A$1" Then Exit Sub
 Application.ScreenUpdating = False
 Dim i As Integer
 Const MaxRow As Integer = 370
 For i = 4 To MaxRow
  If Target.Value < 1 Or Target.Value > 12 Then
   Rows(i).Hidden = False
  ElseIf Month(Cells(i, 1).Value) = Cells(1, 1).Value Then
   Rows(i).Hidden = False
  Else
   Rows(i).Hidden = True
  End If
 Next
 Application.ScreenUpdating = True
End Sub
    • good
    • 0
この回答へのお礼

おかげさまで、For i = 4 To MaxRowをFor i = 3 To MaxRowに書き換えたら出来るようになりました。
マークはやっぱり出ちゃいますかねぇ~
運用で工夫してみたいと思います。
ありがとうございました。

お礼日時:2007/02/16 16:00

>エクセルのオートフィルタを使わないフィルタ方法を伝授してください


なぜオートフィルタを避けているのか、理由はわかりませんが奇異です。
これを使わなければ、エクセルVBAででも使わないとできませんよ。
色々注文をつけると、VBAやそれ以上のことを使わないとできなくなります。
素人がやりたいことを出すと、結構難しい。経験者はその難しさにぶつかることを察知して、1年分を1シートにして、そこから1月分を
抜き出そうというような発想を避けます。
既回答にも、関数式だけで条件該当分を別シートなどに抜き出す式が出ています。しかし恐ろしく長く、内容を理解するのが難しいです。
私はそれを回避するため、作業列を1列使い、条件を満たす(XX月分
という条件)行のみ上から連番を振ります。別シートでその連番を元にINDEXとMATCH関数を使ってデータを持ってきます。それをimogasi
方式として、OKWAVEで沢山回答してます。WEB照会してよければ見てみてください。
ーー
VBAでxx月以外は行を非表示にすることはたやすいが、可視セルを相手にする必要があって、面倒になるかも知れません。
VBAで別シートへXX月分だけ分離するのは難しくありません。その後の
追加削除に連動できませんが。
    • good
    • 0
この回答へのお礼

そんなに難しいこととは判らず大変失礼をいたしました。
ただこの用紙を利用なのみなさんはExcelすら出来ないため、
年齢も50歳後半のため出来るだけ、開けばすぐできる形を作りたかっただけです。
そういう内容の質問をしてはいけないことも十分判りました。
貴重な時間感謝いたします。

お礼日時:2007/02/17 20:39

こんにちわ


月頭を出したいだけでしたら、ジャンプ機能を利用されるといいと思います。

マクロはご無理とのことなので計算式でやろうかとも考えましたが、それだとファイルサイズが大きくなるので、ご質問には反しますがオートフィルタが手っ取り早いと思います。各日付のとなりに1から12の数字を打ち(月の数という意味)、その列でフィルタをかけます。
計算式を入れるか数値を入れるかの違いだと思います。
    • good
    • 0

ご質問者の意図とは若干違うかもしれませんが、フィルタオプションとVBAの組み合わせで比較的簡単に作れるかもしれません。



ただし「月」を条件にしたければ、月日から月を作成する関数(MONTH関数など)で月のみを表示する列を作成しておく必要があります。
そのような列を作れば、ブックのイベントプロシージャ(SheetChage)等に、フィルタオプションの機能をVBAスクリプトとして記述することで可能だと思います。

参考までに(表のサイズなどはそちらで任意に読み替えてください)

Public Sub Data_filter()

Range("A5:S55").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= Range("C1:C2"), Unique:=False

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

  If ActiveCell.Column = 3 Then
     If Cells(2, 3).Value = "" Or IsNull(Cells(2, 3)) Then
        ActiveSheet.ShowAllData
     Else
        Call Data_filter
     End If

  End If

End Sub
    • good
    • 0

簡単な方法としては・・・


シートを2枚に分ける。
Sheet1には1年分のデータ。

Sheet2は1ヶ月分(31行)のみ表示させる。
A4のセルに“=DATE(YEAR(TODAY()),A1,1)”、
A5以下はA4+1,A5+1でOKです。
B列以降は、LOOKUPなりVLOOKUPなりで、Sheet1を参照する、
というのでどうでしょう。
    • good
    • 0
この回答へのお礼

すみません。Excelの入力は得意なのですが、
マクロや関数がまったくわかりません。
皆さんのアドバイスを1ずつやっていますが、
フィルター後「本日の容量」「残容量」「合計」の入力をします。
別のSheetにすると、元データに反映されません。

お礼日時:2007/02/16 13:59

関数でやってみます


「入力結果」シートのA4セルに以下の式を貼り付けて右方向、および下にコピー。A1セルの月は「数値」で入力します(日付ではありません)
各列の書式は適切なものに再設定してください。

=IF(SUMPRODUCT((MONTH(元データ!$A$4:$A$369)=$A$1)*1)-ROW()+4>0,INDEX(元データ!A$1:A$369,LARGE(INDEX((MONTH(元データ!$A$4:$A$369)=$A$1)*ROW(元データ!$A$4:$A$369),),SUMPRODUCT((MONTH(元データ!$A$4:$A$369)=$A$1)*1)-ROW()+4)),"")

うるう年なら5箇所の$A$369は$A$370になります。
    • good
    • 0
この回答へのお礼

マクロも関数もまったくわからない低レベルに親切な回答ありがとうございます。
結果だけを参照するのであれば問題ないのですが、
データを日々入力し、月末に出力をしなくてはなりません。
出来れば、入力後の確認なども行いますので、
月を打ち込んだだけで、1か月分が確認できるほうが
ど素人には単純な操作で出来るほうが良いかと思っています。

お礼日時:2007/02/16 14:14

マクロを使ってもよいですか?(マクロの中で結局オートフィルタを使ってますが)



シートタブを右クリックして「コードの表示」を選択してVBAの画面を開き、下のマクロをコピーして貼り付けてください。
A1に値を入れるとその月だけの表示に変わります。

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address <> "$A$1" Then Exit Sub
 If Target.Value < 1 Or Target.Value > 12 Then Exit Sub
 Dim EndDay As Long
 EndDay = Day(DateSerial(Year(Date), Range("A1") + 1, 1) - 1)
 Range("A3").AutoFilter Field:=1, Criteria1:=">=" & Range("A1").Value & "月1日", Operator:=xlAnd, _
  Criteria2:="<=" & Range("A1").Value & "月" & EndDay & "日"
End Sub
    • good
    • 0
この回答へのお礼

マクロも関数もまったくわからない低レベルに親切な回答ありがとうございます。
質問です。
理想どおり、3月のみの表示になりましたが、
タイトル行に当たる「月日,曜日,本日の容量,残容量,合計」が
消えてしまいます。
やはり「オートフィルター」のマークがB1にも入ってしまうものなのですよね?

お礼日時:2007/02/16 14:42

月日は日付形式ですよね。



C1="月日"
C2=">="&$A$1&"月1日"
D1="月日"
D2="<"&($A$1+1)&"月1日"
で次の処理を「ツール」「マクロ」「新しいマクロの記録」でマクロ化する。
「データ」「フィルタ」「フィルタオプションの設定」
・「データ範囲」「=$A$3:$E$369」
・「抽出条件範囲」「=$C$1:$D$2」
でOK
これで出来たマクロを、ショートカットキーかボタンに登録して実行する。

C1:D2の見た目が問題なら文字色を背景色と同じにすれば表示も印刷もされません。
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセル・オートフィルタを使わず関数で抽出をするには

excelのデータで、該当する項目のある行のみの抽出をオートフィルタもマクロも使用せずに、関数で行う方法があれば教えてください。
    A B
1  桃太郎 123456
2  猿   122222
3  キジ  123555
4  桃太郎 122245
   …
1000 桃太郎 002145
↑このような表で「桃太郎」だけを抽出したいのです。
イメージとしては、ボックスに「桃太郎」と入力するだけで結果が一覧として出るようなものを作りたいのです。
さらにB列が昇順に並び替えされていると、なおよいのです。
随時更新するデータなので、その都度の最新の表が欲しいのです。

オートフィルタ→並べ替えをすれば簡単なのですが、エクセルを全く使えないような人がいる職場環境でして、教えるのが面倒というのとデータがぐちゃぐちゃになったら困るので、誰でも簡単にできる方法はないものかと考えています。
また環境的にマクロはあまり使いたくないのです。

よろしくお願いします。

Aベストアンサー

B列の値は数字のみでしょうか?
こんな方法を考えてみました...

C1を条件値入力セルとしました

(1)下記のセルにそれぞれの式を入れる
D1: =IF(A1<>$C$1,"",B1+ROW()/10000)
E1: =RANK(D1,D:D,1)
F1: =MATCH(ROW(),E:E,0)
G1: =IF(ISERROR(F1),"",OFFSET(A$1,F1-1,0,1,1))
H1: =IF(ISERROR(F1),"",OFFSET(A$1,F1-1,1,1,1))

(2)D1:H1の内容を十分な件数だけ下方向にコピー

(3)D,E,Fの列は非表示にしましょう

(4)C1に桃太郎を与えると G,H列に抽出してソートした結果がでます

QEXCEL関数でフィルタの様にデータ抽出したい

発注品一覧表から社別にデータを抽出した発注シートを関数で作りたいです。
オートフィルタは諸事情あって使用しません。

過去の質問のこちらが、かなり近い回答なのだと思いますが
不勉強で自分用に修正ができませんでした。
http://oshiete.goo.ne.jp/qa/3157199.html

一覧表はこのようになっています。
日付 社名 品目 値段
4/1  C社 鉛筆 50円 
4/1  A社 定規 150円
4/2  C社 ペン 100円
4/2  B社 鉛筆 100円

社別の別シートに日付・品目・値段を一覧から関数で抽出できるでしょうか?
どうぞよろしくお願いします。

Aベストアンサー

拝見いたしましたが参考にされたサイトですが
データが社名順に並んでいますよね。
今回の質問ですが、データが順に並んでいませんので全く違った発想が必要になります。
簡単な方法では、作業列を一列入れる方法がわかりやすいです。
データのシートに作業列も作れないのであれば 配列関数を使うことになりますが
データの量が多くてその式を縦横にコピーして使うですからパソコンの負担も重くなります。

式の意味を理解が難しいかと思いますが
INDEX関数で 範囲の指定の部分を条件で抽出した内容が入るように工夫した式がよく見受けられます
ので一応紹介しておきます
仮に シート名 データ
日付 社名 品目 値段
4/1  C社 鉛筆 50円 
4/1  A社 定規 150円
4/2  C社 ペン 100円
4/2  B社 鉛筆 100円

別のシート
   社名 品目
   C社

日付 社名 品目 値段

とB2セルに抽出したい社名が入っているとして
A4セルに
=INDEX(データ!A:A,SMALL(INDEX((データ!$B$1:$B$100<>$B$2)*1000+ROW(A$1:A$100),),ROW(A1)))&""
と入れて 右へコピー、下へコピーしてみてください。
INDEX((データ!$B$1:$B$100<>$B$2)*1000+ROW(A$1:A$100),)
の部分が理解しにくいと思います。
社名の範囲でC社でなかったら1000倍したとてつもない大きな数字を加える
C社でればそのまま、その行番号 といった架空の列を作成します。
その架空の列の小さい数値の順に 最初のINDEX関数で取り出します
といった感じです。(なかなか文書で説明するのも難しくてすみません)

別案ですが
フィルターオプションの設定(オートフィルターではありません)
をしたほうがシンプルでデータ量が増えた時も勝手に対応してくれます。

拝見いたしましたが参考にされたサイトですが
データが社名順に並んでいますよね。
今回の質問ですが、データが順に並んでいませんので全く違った発想が必要になります。
簡単な方法では、作業列を一列入れる方法がわかりやすいです。
データのシートに作業列も作れないのであれば 配列関数を使うことになりますが
データの量が多くてその式を縦横にコピーして使うですからパソコンの負担も重くなります。

式の意味を理解が難しいかと思いますが
INDEX関数で 範囲の指定の部分を条件で抽出した内容が入るように...続きを読む

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

QExel VBA 別ブックから該当データを検索し、必要なデータを取得する方法について

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数ではなく、マクロで処理を希望します。

自分では、部品表の商品番号をコピーして、コード一覧表で検索し、検索結果の右隣のセル(B列のコード)の値を部品表のC列に貼り付ければよいかと思い、書いてみたんですが…

Sub 別ブックから貼り付ける()
  Dim 検索する As Long
Windows("部品表.xls").Activate
検索する = cells(i,2).Value
Windows("コード一覧表.xls").Activate
ActiveWindow.SmallScroll Down:=-3
Selection.AutoFilter Field:=3, Criteria1:="=検索する", Operator:= xlAnd

と、してみたものの、検索しても、その検索結果の隣のセルのコードをどうやって取得すればいいのかが、わかりませんでした。

基本事項は本で学びましたが、呪文のようなコードはよく理解できません。懸命にネットで検索して、訳して理解する努力をしてはいますが。

どうぞよろしくお願いします。

部品表というブックがあります
A列に商品名、B列に商品番号が入力してあります。C列のコードは未入力です。
A列     B列     C列      
商品名  商品番号  コード
モータ  U-1325-L  
ホルダ  R-134256

また、コード一覧表という別のブックには、A列に商品番号と、B列にコードが、何千件も入力されています。

やりたいことは
部品表のC列のコード欄に、コード一覧表ブックから商品番号と一致するコードを貼り付けしたいのです。

部品表は、何百種類もありますので、関数...続きを読む

Aベストアンサー

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks.Open("C:\★★\コード一覧表.xls") '★要変更★
 I = 2
 Do While Range("A" & I).Value <> ""
  ThisWorkbook.Worksheets("Sheet1").Range("C" & I).Value = Application.VLookup(ThisWorkbook.Worksheets("Sheet1").Range("B" & I).Value, xlBook.Worksheets("Sheet1").Range("A2:B65535"), 2, 0)
  I = I + 1
 Loop
 xlBook.Close
 Application.ScreenUpdating = True
 MsgBox ("完了")
End Sub

こんにちは。
とりあえず実用性も踏まえました。
メインの動作はワークシート関数のVLOOKUPをVBA上で使用していますので理解はしやすいかと思います。
また、質問文から察するに「部品表.xls」と「コード一覧表.xls」の両方を開いて処理されていますが「コード一覧表.xls」はプログラム内で開いて閉じているので実行するときは「コード一覧表.xls」は閉じて置いてください。
Option Explicit
Sub Sample()
 Application.ScreenUpdating = False
 Dim I As Long
 Dim xlBook
 Set xlBook = Workbooks....続きを読む

QエクセルVBA 複数の条件を含む対象を抜き出す。

エクセルVBAについて質問です。
エクセルのバージョンは2003と2007を主に使用しています。

下記の様なデータがあるときに、部活が「野球」でかつクラブは「囲碁」に入っている生徒の学籍番号を別のシート(Sheet2)のB3から下に順にリスト化するマクロがどうしても出来なくて困っています。
find next等を使うのでは無いかと色々してみましたが上手く出来ない現状です。

<sheet1>
   A      B      C       D    E

1 学籍番号 学年    名前     部活   クラブ
2 2222222   1   山田 太郎  野球   囲碁
3 9854923   2   吉田 次郎   剣道   絵画  
4 1111111   3   佐藤 三郎  野球   囲碁
5 8888883   1   米山 権蔵  卓球   囲碁

Aベストアンサー

こんばんは!
Sheet1のA列(学籍番号)のみをSheet2のB3セル以降に表示すれば良いわけですね?
一例です。

画面左下のSheet1のSheet見出し上で右クリック → コードの表示 → VBE画面に
↓のコードをコピー&ペーストしてマクロを実行してみてください。

Sub test()
Dim i, k As Long
Dim ws As Worksheet
Set ws = Worksheets(2)
k = 2
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 4) = "野球" And Cells(i, 5) = "囲碁" Then
k = k + 1
ws.Cells(k, 2) = Cells(i, 1)
End If
Next i
End Sub

こんな感じではどうでしょうか?m(_ _)m

QEXCEL VBAマクロ作成で、他のEXCELからデータを取り込みたい

メインプログラム(EXCEL VBA)より、
他のフォルダーにあるEXCELの項目の内容を取り込みたいです。
たとえば他のフォルダーのEXCELのRange("A2:A3").ValueをメインプログラムのRange("C2:C3").Valueにセットしたい時です。

・コマンドボタン押したら、どこのEXCELから取り込むかのポップアップ(?)は、表示はできてます。
・作業者が選んだパスとブックもMsgBoxで表示できてるので、もらう相手の場所も取得できてます。

・となると次はOPEN,INPUTですか?
テキストデータの取り込みですと、Inputでそのバッファを定義してるのですが、なんか違うような。。。

よろしくお願いします!

Aベストアンサー

私がやる方法です。

Dim writeSheet As Worksheet ' 自分自身の書き出し先シート
Set writeSheet = ThisWorkbook.Worksheets(1) ' Sheet1 を参照

Dim readBook As Workbook ' 相手ブック
Set readBook = Workbooks.Open(filename) ' 相手ブックを開いて参照
Dim readSheet As WorkSheet ' 相手シート
Set readSheet = readBook.Worksheets("sheetName") ' 相手シートを参照
' または Set readSheet = readBook.Worksheets(sheetIndex)

' 例えば
writeSheet.Cells(1, 1).Value = readSheet.Cells(2, 2).Value ' 相手シートの B2 の値を自分自身の A1 に書き込む

readBook.Close False ' 相手ブックを閉じる
Set readSheet = Nothing
Set readBook = Nothing

私がやる方法です。

Dim writeSheet As Worksheet ' 自分自身の書き出し先シート
Set writeSheet = ThisWorkbook.Worksheets(1) ' Sheet1 を参照

Dim readBook As Workbook ' 相手ブック
Set readBook = Workbooks.Open(filename) ' 相手ブックを開いて参照
Dim readSheet As WorkSheet ' 相手シート
Set readSheet = readBook.Worksheets("sheetName") ' 相手シートを参照
' または Set readSheet = readBook.Worksheets(sheetIndex)

' 例えば
writeSheet.Cells(1, 1).Value = readSheet.Ce...続きを読む

QエクセルVBAでフィルタ抽出部分のみのコピー

エクセルVBAで売上帳を作成していますが、オートフィルタでデータ抽出した後、表示されている行のみをコピーして別シートに貼りつけるにはどうすればよいのでしょう?

別シートは指定したセルに値のみの貼り付けをしたいと思っています。

宜しくお願いします。

Aベストアンサー

こんにちは
マクロの記録で作成した一例です。コメントを読んで、適当にアレンジして下さい。

Option Explicit
Sub SampleMacro1()
'
' SampleMacro1 Macro
' マクロ記録日 : 2009/3/13
'
 'フィルター部分
 Selection.AutoFilter Field:=1, Criteria1:="=ほげほげ", Operator:=xlAnd
 '可視セルの選択
 Selection.SpecialCells(xlCellTypeVisible).Select
 '選択範囲のコピー
 Selection.Copy
 'コピー先のシート&セル選択
 Sheets("Sheet2").Select
 Range("A1").Select
 'ペースト
 ActiveSheet.Paste
 'コピー元シートに戻りコピー状態解除
 Sheets("Sheet1").Select
 Application.CutCopyMode = False
 Range("A1").Select
End Sub

外してたら、ごめんなさい

こんにちは
マクロの記録で作成した一例です。コメントを読んで、適当にアレンジして下さい。

Option Explicit
Sub SampleMacro1()
'
' SampleMacro1 Macro
' マクロ記録日 : 2009/3/13
'
 'フィルター部分
 Selection.AutoFilter Field:=1, Criteria1:="=ほげほげ", Operator:=xlAnd
 '可視セルの選択
 Selection.SpecialCells(xlCellTypeVisible).Select
 '選択範囲のコピー
 Selection.Copy
 'コピー先のシート&セル選択
 Sheets("Sheet2").Select
 Range("A1").Select
 'ペース...続きを読む

Q条件にマッチする行を抽出するVBAを教えてください

アイデア、またはVBAプログラムの例を教えていただきたく、質問させていただきます

excelで、添付画像のようなリスト管理表を作っています。
リストは600行近くになります。
やりたいことは、D3またはE3に商品名または保管庫を入力すると、リスト内から、合致する行だけが抽出される、というもの。
D3とE3は、どちらか片方にのみ条件が入る。D3とE3の内容を変更するとリアルタイムで抽出結果も変更されるようにしたい。
触る人が初心者なので、難しい作業を一切せずに、D3またはE3を打ちかえるだけで必要な項目だけのリストとなり、印刷するだけでいいようにしたいわけです。

本来ならオートフィルタですればいい話ですが、どうしてもD3という離れたセルの入力内容で抽出したいのです。

VBAでなく、D3のセル内容を使ってD8~のオートフィルタが行えるなら、それが一番理想です。
が、自分でやってみた限りはできませんでした。

フィルタオプションならどうかとやってみたところ、一回目は抽出できました。しかし、D3またはE3の条件を変更しても、リアルタイムで抽出結果が切り変わらない。
フィルタオプションの抽出結果を別のセルに出せばいいのですが、そうすると無駄な情報が残り、ただ印刷しただけでOK・・というわけにいきません。(印刷範囲を区切るとかでなく、シートの見栄えが必要な情報だけにならないと…扱う初心者が混乱します)


自分なりには、VBAにより、 D3・E3のセル内容が書き換わったらフィルタオプションの抽出結果をいったん同シートの別セルに出し、抽出結果部分だけを別のシートにカット&ペースト成形。そのシートを印刷させればよい。
という考えになりましたが、やってみたら、なぜか別のブックに同じものが形成され、抽出した結果だけ単独のデータにできません。

そもそももっと良いアイデアがあればそれをおしえていただきたい。
あるいは、VBAで目的達成できるように問題点をご指摘ください。


一応、プログラムを書いておきます



■添付画像のデータが入っているシート(『一覧』という名前のシート)内コード

Private Sub Worksheet_Change(ByVal Target As Range)
'

If Target.Column = 4 Then
If Target.Row >= 3 And Target.Row <= 3 Then

Call Filter
Call copy

End If
End If

End Sub

■サブルーチンFilter() 標準モジュールに記載
Sub Filter()

' Filter Macro

'フィルタオプションを使って同シート内「D1100」以降に抽出結果を出します
ActiveWorkbook.Worksheets("一覧").Select

'一覧表はD7~F1000。検索条件はD2~F3までの範囲に名前を付けたもの
Range("一覧表").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"検索条件"), CopyToRange:=Range("D1100"), Unique:=False

Range("A1").Select
End Sub


■サブルーチンcopy() 標準モジュールに記載
Sub copy()
'
' copy Macro
'
'抽出された内容(45行目~100行目まで)を別のシートにコピーします

ActiveWorkbook.Worksheets("一覧").Select
Rows("45:100").Select
Selection.Cut
ActiveWorkbook.Worksheets("抽出結果").Select
Rows("4:4").Select
Selection.Insert Shift:=xlDown
Range("A1").Select


End Sub

アイデア、またはVBAプログラムの例を教えていただきたく、質問させていただきます

excelで、添付画像のようなリスト管理表を作っています。
リストは600行近くになります。
やりたいことは、D3またはE3に商品名または保管庫を入力すると、リスト内から、合致する行だけが抽出される、というもの。
D3とE3は、どちらか片方にのみ条件が入る。D3とE3の内容を変更するとリアルタイムで抽出結果も変更されるようにしたい。
触る人が初心者なので、難しい作業を一切せずに、D3またはE3を打ちかえるだけで必要な項目だ...続きを読む

Aベストアンサー

追記:
では、当方で検証したサンプルコードを載せますので、ご参考に。結果提示用に「抽出結果」と名付けたまっさらなシートを予め用意しておいてください。

と、その前に注意点。
ご質問内容では、シートモジュールや標準モジュール等、複数のモジュールにコードが分散していますが、今回の処理内容では、モジュールを分ける意味がありません。シートモジュールのワークシートチェンジイベント1本で十分です。従って、ご案内するコードは、一覧表のあるシートのシート見出しを右クリック→コードの表示から呼び出した画面に書き込み、入力が終わったら、ファイルタブ→終了してexcelに戻る、としてください。

それと、クライテリアを使うと、倉庫1の検索で倉庫10以降もピックアップされてしまうので、1は全角で10以降は半角にするなど、元ネタに区別をしてください。

また、利用者のなかにビギナーがいるのであれば、セルのロックと保護を使い、一覧シートのD3:E3しか操作出来ないようにする、入力規則を使って、商品1,商品2といったリストから選ばせる、等の工夫も考えられます。それらをどう併用するかによって適切なコードも変わってきますので、細部はご自身で調整してください。

Private Sub Worksheet_Change(ByVal Target As Range)

If Application.Intersect(Target, [D3:E3]) Is Nothing Then Exit Sub

Worksheets(”抽出結果”).[A1:C1000].ClearContents

Range(”一覧表”).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
(”検索条件”), Copytorange:=Worksheets(”抽出結果”).Range(”A1”)

End Sub

追記:
では、当方で検証したサンプルコードを載せますので、ご参考に。結果提示用に「抽出結果」と名付けたまっさらなシートを予め用意しておいてください。

と、その前に注意点。
ご質問内容では、シートモジュールや標準モジュール等、複数のモジュールにコードが分散していますが、今回の処理内容では、モジュールを分ける意味がありません。シートモジュールのワークシートチェンジイベント1本で十分です。従って、ご案内するコードは、一覧表のあるシートのシート見出しを右クリック→コードの表示から呼...続きを読む

Q別のシートから値を取得するとき

Worksheets("シート名").Activate
上記のを行ってから別シートの値を取得するのですが、
この処理を行うと指定したシートへ強制的にとんでしまいます。。。

※イメージ
For ~ To ~
  Worksheets("シートA").Activate
  シートAの値取得
       :
  Worksheets("シートB").Activate
  シートBの値取得
Next

このイメージ処理を行うとものすごい勢いで画面がチカチカします。。。
シートを変えずに他のシートから値を取得する方法はないのでしょうか。
教えてください!

Aベストアンサー

Worksheets("シートA").Range("A1")

みたいな感じでできませんか?

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む


人気Q&Aランキング