こんにちは、Excelの関数で教えてください。
いま注文書を作成しています。A1からE10の
範囲でタイトル行含み10行作成し、A~Eには
それぞれ型番、商品名、単価、数量、合計金額が
入っているとしてください。つまり商品の種類は
9種類です。
ここで、たとえば2行目と6行目のみ注文がある
場合、2行目と6行目だけ数量の欄に入力されます。
すると、印刷時に2行目と6行目だけ印刷されるよう
制限をかけたいのですが、どのような関数を使えば
できるでしょうか?
メンテナンス等を考慮し、できればマクロを使わず
関数だけで実現したいのですが・・・。
よろしくお願いします。
A 回答 (7件)
- 最新から表示
- 回答順に表示
No.7
- 回答日時:
#1です。
#4、#5、#6の方はかなり工夫をされた回答で大変参考になると思います。また、#6の方の「…VBAで自動化しないと、関数では自動化省力化しないので前途多難なように思います。」とおっしゃるのはもっともだと思います。関数の中に関数を入れる等の多様化をするほど、誤りがあったときに、誤りを探すのに苦労します。(これは私だけの経験ですが)
#1の回答に対する補足での「フィルタをかけてから印刷するといった2段階の手順を自動でする」といったことをお望みであれば、やはり一つのマクロにしてしまう方が、メンテナンス等を考慮された方法だと思います。(私だけの考えかもしれませんが…。)
No.6
- 回答日時:
A2:B8に下記きデータがあるとする。
注文品名がA列、注文したのはB列に数字のある行で、注文数はその数とする。C列はワーク列です
A列 B列 C列
a0
b231
c1
d1
e342
f2
g23
C2セルに、=COUNTA($B$1:B2)と入れて、下方向に式を複写する。
結果は上記のとおり。注文した品の行で1だけカウントアップしてます。
ここが味噌です。
普通は抜き出し先はSHeet2などですが、説明を簡単にするため、すぐ横に注文品と注文数を抜き出します。
F2セルに=OFFSET($A$1,MATCH(ROW()-1,$C$1:$C$100,0)-1,0)
F3以下に式を複写する。
G3に=OFFSET($A$1,MATCH(ROW()-1,$C$1:$C$100,0)-1,1)と入れて下方向に
式を複写します。F3を横にG3に複写して最後の引数を0->1に変えればよい。
さて上記の理屈がわかったとして、問題はF3:Gxの範囲において、注文数を超える下の行にはエラー表示が出るが
ISERRORなどで判別して、出ないようにしてください。
注文請け書(印刷するシート)は別シートでしょうから、F、G列をSheet2などへ持っていくとき式が見かけ上、変わります。Sheet1!$C$3:$c$100などのように参照しますので、この点注意してください。
もう1つ注文書が多数ある場合、VBAで自動化しないと、関数では自動化省力化しないので前途多難なように思います。
No.5
- 回答日時:
#3訂正です。
質問文を読み直してみると、商品は固定のようですね?
つまり、商品名があると思われるA列をカウントしてしまうとまずいですね。
失礼しました。
私が先ほど記載しました
=OFFSET($A$1,0,0,COUNTA($A:$A),5)
という数式の
$A:$Aという部分、数量があるD列にするべきでした。
=OFFSET($A$1,0,0,COUNTA($D:$D),5)
さらに、一つのブックに何枚もシートがあり、同じ注文書を作成していて、同じように印刷したい場合は
=OFFSET(!$A$1,0,0,COUNTA(!$D:$D),5)
としてください。
No.4
- 回答日時:
こんばんは。
注文書があるシートをSheet1 とさせていただきます。そして、ここでは、印刷シートを、Sheet2 にします。
配列数式を使った例:
別のシートに、
A B C D E
1: 型番 品名 単価 数量 合計金額
A2:~
=IF(COUNT(Sheet1!$D$2:$D$10)>=ROW(A1),INDEX(Sheet1!$A$1:$E$10,SMALL(IF(Sheet1!$D$2:$D$10>0,ROW($D$2:$D$10),""),ROW(A1)),COLUMN(A1)),"")
という式を置いたら、配列数式ですから、一旦入力したら、F2を押して、『ShiftとCtrlを押しながらEnterキー』を押します。
後は、フィルハンドルで、式をコピーしてしまいます。
配列数式を使わない方法:
(ここの掲示板では、配列数式を使うと、文句をつける人がいるようですから(^^;)
同じく別シートを使います。
補助列を使って、
例:
G2 ~G10 まで
=IF(Sheet1!D2>0,ROW(),"")
フィルハンドルコピー
H2~H10 まで
=IF(COUNT($G$2:$G$10)>=ROW(A1),SMALL($G$2:$G$10,ROW(A1)),"")
これで、ここで、出てきた行数を、
A2~
=IF($H2<>"",INDEX(Sheet1!$A$1:$E$10,$H2,COLUMN(A1)),"")
これで、集約できます。
No.3
- 回答日時:
エクセル2000で検証してみたので、大丈夫だと思いますが・・。
まず、挿入~名前~定義 と選択。
名前の定義を作成するウインドウが開きます。
名前は「Print_Area」です。
参照範囲には
=OFFSET($A$1,0,0,COUNTA($A:$A),5)
と入力してOKボタン
これで、注文が2~5行目なら、A1からE5を印刷範囲として印刷し、注文が2~10行になったら、A1からE10を印刷範囲として印刷できるはずです。
注意点が2つあります。
1、「Print_Area」という名前は、この通りこのままでお使いください。もちろん「」は抜いて。
2、参照範囲に使った関数は、たとえば2~6行目に注文があった場合、A1~A6全て埋めて(空欄ではない状態)おかないと、参照が狂います。
上記関数は、A1を含めてA列に値があるセルの数の行数で、E列まで参照(この場合選択という表現の方がわかりやすいかも)します という意味です。
つたない説明ですみません。
参考URLには、私がこの関数を覚えたサイトをご紹介します。
参考URL:http://www2.odn.ne.jp/excel/
No.1
- 回答日時:
A1からE10まで全てをドラッグで範囲指定をします。
[データ]-[フィルタ]-[オートフィルタ]を処理します。
A1からE1までの各タイトルに[▼]が出ます。
D1のセル(つまり数量)の[▼]をクリックし、(空白以外のセル)を選ぶと数値の入った行のみが表示され、このまま印刷すると良いです。
元に戻すには、もう一度、[データ]-[フィルタ]-[オートフィルタ]を処理します。
関数ではありませんが、割と簡単にできると思います。
この回答への補足
ご回答ありがとうございました。すいません、フィルタの処理を
自動で(都度、注文者にフィルタをかけて印刷
させるのではなく)できる方法はありませんか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) エクセルの表について 3 2023/04/14 18:00
- Excel(エクセル) エクセルの印刷範囲をページ単位で可変にする方法 3 2022/05/23 13:04
- Visual Basic(VBA) VBAで、1つのエクセルで、2つのシートからもう1つのシートに条件のある転記コードを教えてください。 1 2023/03/16 18:07
- Excel(エクセル) Excel 関数 vlookupなどの使い方について質問です。 シート1に品番、商品名、単価、発注条 6 2022/06/15 19:16
- Excel(エクセル) グループの最後の行に書式、計算式なども同じ行を追加するマクロを教えてもらえませんか。 7 2022/05/18 10:13
- Excel(エクセル) 列を自動で追加したい 3 2022/07/11 12:58
- その他(データベース) pythonでsqlight勉強中、クエリー結果の利用法教えて下さい 1 2022/04/28 20:38
- Visual Basic(VBA) VBA シート間の転記で、条件の追加コードの書き方について教えて下さい。 13 2023/02/26 09:31
- 仕事術・業務効率化 文書作成の際の書体や書式について 1 2022/08/29 10:01
- Excel(エクセル) ExcelのIF関数について 4 2023/05/24 12:54
このQ&Aを見た人はこんなQ&Aも見ています
-
「環境が人を育てる」って本当?環境によって人格や生き方は本当に変わるのか
環境が人生に与える影響は実際どれほどのものなのか、専門家の田宮由美さんに伺った。
-
特定のセルに数値が入っているシートのみ印刷する方法
Excel(エクセル)
-
エクセルで空白の部分を印刷範囲外にする方法
Excel(エクセル)
-
エクセル VBA 条件つきの印刷
Excel(エクセル)
-
-
4
空白セル(関数入ってます)を印刷しない
Excel(エクセル)
-
5
EXCELで特定のセルに表示された項目をヘッダーやフッターに出力するには
Excel(エクセル)
-
6
エクセルで文字を含む式に、カンマ(,)をつけるには??
Excel(エクセル)
-
7
ヘッダー
Excel(エクセル)
-
8
エクセルでデータがあるシートだけ印刷するマクロ
Excel(エクセル)
-
9
エクセルマクロについて データが入力されている列までを印刷範囲とするマクロについてご教示ください。
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルの関数について教えて...
-
Excelカスタム関数(アドイン登...
-
Excelデータをコピペして、ペー...
-
Excel関数-文字列で自動作成さ...
-
スプレッドシート、Excelでの数...
-
Excelで50個のセルに同じ文字を...
-
Microsoft Officeの中古は信用...
-
スプレッドシートで使う数式を...
-
エクセルVBA、別ブックへ転記す...
-
エクセルで会社の従業員のデー...
-
エクセルで不等号記号(≠)が上に...
-
エクセルの表で1年間の曜日を...
-
A列とB列を参照してC列に連番を...
-
エクセルの空欄をつめて、次の...
-
エクセルでの特別な文字を上に...
-
エクセルでセルに標準で入力さ...
-
エクセル日付 文字列の関数がエ...
-
エクセル2013で月間勤務表から...
-
エクセルの日付を編集する
-
EXCELの質問です 119から足した...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報