初めての店舗開業を成功させよう>>

いつも、大変お世話になっております。

vbaの記述で配列関数の部分が分からず投稿させていただきました。ご指導お願いいたします。

ご迷惑をおかけしないように、注意を払っておりますが、不行き等ございましたら、ご容赦ください。


具体的なコードと致しましては、下記です。

With ThisWorkbook.Worksheets("chart").Range("C4").Resize(myrows, 200)
.Formula = "=MIN(IF((作業NO=$A4)*(C$1<=作業日)*(作業日<=C$1+6),NO))"
End With

式を埋め込んで、一つ一つのセルを選択して「ctrl+shift+enter」ボタンを押せば、いいのですが手間なので、

自動的に一括して「ctrl+shift+enter」ボタンを押してもらえるような記述内容の変更の仕方をご指導いただけないでしょうか

当方、excel2000を使用しています。

よろしくお願いいたします。

「excel配列関数ctrl+shift+」の質問画像

A 回答 (3件)

とりあえず確実に簡単にできる方法としては,次のようなマクロにします。



with thisworkbook.worksheets("chart")
.range("C4").formulaarray = "=MIN(IF((作業NO=$A4)*(C$1<=作業日)*(作業日<=C$1+6),NO))"
.range("C4").copy destination:=.range("D4").resize(1, 199)
.range("C4").resize(1,200).copy destination:=.range("C5").resize(myrows-1, 200)
end with




マクロ以前にエクセル画面で,どうやってその式を使うかと言えば
1.C4セルに数式を記入してCtrl+Shift+Enterで入力する
2.右に下にコピー貼り付ける
という手順を行います。
上述は,この「出来る手順」をそのままマクロに仕立てたものです。

先に範囲を選択してその式を記入し,Ctrl+Shift+Enterしても,意図した計算結果は得られません。
つまり
セル範囲まとめて.formulaarray = "=今の配列数式"
というマクロを書いても,上手く行かないということです。
    • good
    • 0
この回答へのお礼

ありがとうございました。

同じセルにコピーの行き先(destination)を指定できればいいのに、と思いましたが。

やりたいことはできました。

ありがとうございました。

お礼日時:2011/11/22 15:07

ここの質問コーナーに答えている程度では、VBAで配列数式を問題にする例は無かった。


VBAでやるなら個々のセルをプログラムを組んで処理する、別の方法があるものだ。そちらを使うのが筋だろう。
配列数式の関数式をセルに埋め込むなど邪道だろう。
関数式だってVBAでセルに埋め込もうとは思わない。
出来るとしても、凝った方法を使いすぎだと思う。質問者はそこまでVBAの熟練者なのか。
他の方法ではできないのか。
ーー
配列数式を離れて、やりたいことをデータ例をあげて、説明してみたらどうすか。
読者に数式を読み解かせ、Resizeしてみたりして、なにをやりたいか意味がわかりにくい。
>式を埋め込んで、一つ一つのセルを選択して「ctrl+shift+enter」ボタンを押せば、いいのですが手間なので、
なぜそうなるのか情況を説明してないのでは。?
MINならMINを求めるセルだけで良いのでは。
ーーー
例えば、先日のわたしの回答例だが
A2:C9の名前を「あ1」とつけた場合
Sub test01()
Worksheets("Sheet2").Range("a2:C9").FormulaArray = "=あ1"
End Sub
は、コピーと同じで
Sub test02()
Worksheets("Sheet1").Range("a2").CurrentRegion.Copy Worksheets("Sheet2").Range("a2")
End Sub
出できると思う。
ーー
よくある条件付き加算の場合
例データ
A2:B10
a1
b2
c3
d4
a5
d6
c7
a8
s9
で、aの行のB列を足すのは、配列数式操作は 例えばC12セルに、=SUM(IF(A2:A10="a",B2:B10,0)) と入れて Shift+CTRL+ENTERで14になるが、
例えばD12にVBAでセットする場合は 
Sub test03()
Range("D12").FormulaArray = "=SUM(IF(A2:A10=""a"",B2:B10,0))"
End Sub
の実行で出来た。
こういうのは参考になるかな?
質問の場合はどううまく行かないのか。
    • good
    • 0
この回答へのお礼

ありがとうございました。参考になりました。

レベルを上げて、質問の仕方をあげていきたいと思います。

お礼日時:2011/11/22 15:08

.Formula = "=MIN(IF((作業NO=$A4)*(C$1<=作業日)*(作業日<=C$1+6),NO))"



.FormulaArray = "=MIN(IF((作業NO=$A4)*(C$1<=作業日)*(作業日<=C$1+6),NO))"

ということとは違いますか。
    • good
    • 0
この回答へのお礼

解答ありがとうございます。私の説明不足で、申し訳ございません。

ご教授いただいた記述(.FormulaArray)ですと、resizeしたほかのセルも全て同じ値(絶対参照)になってしまいます。

これを、解消するためには、Formula で式を埋め込むしかないと思うのですが、その後で、まとめて 各セルに「ctrl+shift+enter」ボタンをおすなんて、記述ができればと思います。

お礼日時:2011/11/20 18:20

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

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

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

QExcelで配列数式を入力できない

WinXP-SP3,Excel2003です。
配列数式は、例えば任意のセルに、
=SUM( IF( A1:A10="○", B1:B10, ))
などと入力し、引き続きCtrl+Shift+Enterで確定するそうですが、Ctrl+Shift+Enterを叩いても何の反応もありません。やり方のどこが間違っているのでしょうか。

Aベストアンサー

Ctrl+Shift+Enterが他の何かのショートカットに設定されてるとか

C:\Documents and Settings\ユーザー名\Application Data\Microsoft\Excel
に変なファイルがあったら削除してみるとか…

QExcel、sumifはありますが、minif、maxifはどうしたら?

sumif、countif関数はありますが、minif、maxif関数的なものはどうしたら実現できるのか分かりません。VBAならできますが、関数で実現する方法がありましたら教えてください。

Aベストアンサー

MIN/MAX関数とIF関数で配列数式にするぐらいでしょうか。
例えば、A1:A10に「○」文字列のあるB1:B10のMIN値
 =MIN(IF(A1:A10="○",B1:B10))として入力完了時に、shift+ctrl+enterキーを同時押下して下さい。

QMAXIFはどうすればいい?

AVERAGEIFはありますが、maxでほしいのです。どうやって作れますか?

詳細:
B列が「あ」のデータだけ抜き出し、A列のmax値を得たいのです。
A    B
1   あ
2   い
4   あ
8   え
15  い
この例ではmax値は4です。

Aベストアンサー

>MAXIFはどうすればいい?
MAX関数の括弧内(配列)を前処理しなければなりません。
前処理の数式=(B1:B5="あ")*A1:A5
従って、=MAX(B1:B5="あ")*A1:A5 → MAX({1;0;4;0;0}) → 4
Excelでは計算結果を配列で返すことを要する数式を入力時にCtrlとShiftを押しながらEnterキーを打鍵することになっています。
確定した状態を数式バーで見ると{=MAX(B1:B5="あ")*A1:A5}のように数式を大括弧で括られています。

SUMPRODUCT関数の引数としてMAX関数の返り値を設定するとEnterキーで確定しても目的通りの値が返ります。
=SUMPRODUCT(MAX((B1:B5="あ")*A1:A5))
どのような数式を使っても結果が目的に合えば良いのでお好きな方法で解決してください。

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行までとしていますが、必要に応じて変更して下さい

Qエクセルでの複数条件下での標準偏差の求め方

教えてください。エクセル2007を使用しています。僕はエクセル初心者ではありませんが、玄人でもない中級者くらいだと思います。早速ですが、例えばA列に男か女かの性別、B列に日本、アメリカなどの国籍、C列に東京、フロリダなどの州、県、D列に右利きか左効きか、E列に年齢が書いてある表において、「男、日本、埼玉、右利き」の人の「年齢」の「標準偏差(STDEV)」を求めようとしたとき、計算する方法がわかりません。ソートをかけて求める方法も考えましたが、内容や位置がコロコロ変わるため、向いていないと思ってます。平均値ならAVERAGEIFSで出せますし、合計ならSUMIFSがあると思います。1つの条件(たとえば、「日本」の「年齢」の標準偏差)ならば、なんとかできますが、このような場合の関数はあるのでしょうか?もしなければ、どのように算出するのか教えて頂ければありがたいです。よろしくお願いいたします。

Aベストアンサー

方法1:
=STDEV(IF((A1:A100="男")*(B1:B100="日本")*(C1:C100="東京")*(D1:D100="左"),E1:E100))
と数式バーに記入して,コントロールキーとシフトキーを押しながらEnterで入力します


方法2:
STDEVの基本式
=SQRT((N*Σ(x^2)-(Σx)^2)/(N*(N-1)))
で計算します(関数のヘルプを参照の事)

NはCOUNTIFS関数,ΣxはSUMIFS関数で計算できますが,Σ(x^2)については
=SUMPRODUCT((A1:A100="男")*(B1:B100="日本")*(C1:C100="東京")*(D1:D100="左"),E1:E100,E1:E100)
といった具合に求める必要があります。

Qチェックボックスの箱のサイズとフォントサイズは変更できるの?

こんにちわ。

エクセル2000でチェックボックスを作成したのですが、
やや、小さいのでサイズを大きくしたいのです。
ボックスサイズ、ボックスの横に入れる文字の
フォントサイズは大きくできるのでしょうか?
もし、できるのなら方法を教えてください。

よろしくおねがいします。

Aベストアンサー

コントロールツールボックスのチェックボックスならプロパティにFontがあるので変更できます。

フォームのチェックボックスやオプションボタンのフォントサイズは変更できなかったと思いますが、変更したように見せかけはできます。使用上、特に問題はないと思います。(だからダイアログにフォントがない?)

チェックボックスの位置によって、
(1)チェックボックス(四角)がセルの中央にある場合
 チェックボックスのテキストを必要な長さの空白にしてしまいます。
 チェックボックス(四角)の右横のセルにチェックボックスの説明のテキストを入れます。
 セルの書式設定でフォントが変更できます。
(2)チェックボックス(四角)をセルの境目などにセットする場合
 チェックボックスのテキストを必要な長さの空白にしてしまいます。
 図形描写からテキストボックスを選択し、チェックボックスの説明のテキストを入れます。
 テキストボックスは『線なし』にしておきます。2行で表示したりもできます。
 チェックボックスとテキストボックスを重ねて、見た目を調節します。
 テキストボックスを選択し、右クリック→順序→最背面へ移動 とします。

ご参考に。

コントロールツールボックスのチェックボックスならプロパティにFontがあるので変更できます。

フォームのチェックボックスやオプションボタンのフォントサイズは変更できなかったと思いますが、変更したように見せかけはできます。使用上、特に問題はないと思います。(だからダイアログにフォントがない?)

チェックボックスの位置によって、
(1)チェックボックス(四角)がセルの中央にある場合
 チェックボックスのテキストを必要な長さの空白にしてしまいます。
 チェックボックス(四角)の右横のセ...続きを読む

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すると関係ないセルの値が返るので、
場...続きを読む

QExcel 数式 一括変更・一括反映について (初心者的質問なのですが・・・)

Microsoft Excel 数式の一括変更・一括反映(という表現が正しいのかわかりませんが・・・)について質問させていただきます。
お分かりになる方いらっしゃいましたら、ご教示のほどよろしくお願い致します。
使用しているのはOffice 2003(パーソナル)です。
言葉ではうまく説明しにくいので、作成した表の例を記入させていただきます。

............................. 1月             2月 ・・・・
     単価  数量  合計金額
品名1 .. (B1) ... (C1) ...... (D1) .......(← B1, C1などは座標番号)
品名2 .. (B2) ... (C2) ...... (D2)
品名3 .. (B3) ... (C3) ...... (D3)
品名4 .. (B4) ... (C4) ...... (D4)
品名5 .. (B5) ... (C5) ...... (D5)

合計 .....................   (=D1+D3+D5) ← (品目1・3・5の合計)
最大値.....................   (=MAX(D1,D3,D5)) ← (品目1・3・5のうち最大値)
最小値.....................   (=MIN(D1,D3,D5)) ← (品目1・3・5のうち最小値)

・上記の例ですと、品目1, 3, 5の合計を出す数式が「D6」の座標に入っています。
 最大値を出す式が「D7」に、最小値を出す式が「D8」に入っています。
・次に、2月の表も同じようになるように、数式をコピーします。
 ・すると、2月の合計欄(「G6」の座標)は(G1+G3+G5)となります。  最大値の欄(「G7」の座標)は「=MAX(G1,G3,G5」、最小値の欄(「G8」の座標)は「=MIN(G1,G3,G5」に変わります。
  以下、3月~12月まで同じように数式をコピーします。ここまではOKなのです。

・その後、1月の合計欄を「品目1・3・4の合計」に変えたいと思い、数式を「=D1+D3+D4」に変更しました。
最大値最小値も「MAX1,3,4」「MIN1,3,4」に変更しました。
・その後、また同じように2月~12月まで複写を繰り返すのが大変なので(実際の表はは右に何十行もあり、同じような表が何十個もあるため)、
<1月の計算式を変えると、2月以降もそれに応じて(自動的に)すべて計算式が変わるようなテンプレート>にしておきたいと考えました。

↑ここがうまく説明できないのですが・・・意味がおわかりいただけるでしょうか?

つまり、1月の計算式を<D列の1・3・4合計>に変更すると、自動的に2月~12月も同じように<G列~の1・3・4合計>になるようにしたいのです。

この場合、2月~12月の合計欄(G6、J6、M6・・・)にはどのような数式(or参照式?)を入れれば良いのでしょうか?
ちなみに、「=D6」と入れると、1月の合計欄に入っている数値自体を引っ張ってきてしまい、だめです。

「=XXX(D6)」のように、単にD6の座標を参照すれば済むようなシンプルな方法はないのでしょうか?
試しに「XXX」の部分にいい加減にいろいろな関数を入れてみたのですが(「=VLOOKUP(D6)」など)だめでした。
エクセルのヘルプ機能で「数式の複写」とか「相対参照、絶対参照」などの項目を読みましたが解決しませんでした。
本当は市販のガイドブックなどでしっかり勉強すべきなのでしょうが、自分の知識ではすぐには難しそうなので、
ご存じの方がいらっしゃいましたらご教示をお願いできればと考えました。
初歩的な質問で(説明も長々と書いてわかりにくくて)恐縮ですが何とぞよろしくお願い致します。m( _ )m

ちなみに、昨日OKWaveで同じ質問を投稿しまして、D1+D3+D5(&その各右側)の座標については「IF関数で対応してはどうか」というアドバイスをいただき、
この欄については一応目的を満たせたのですが、MAX, MINの欄はIF関数では対応できません。よろしくお願い致します。
(その時のQ&AのURL)
 http://okwave.jp/qa4936788.html

Microsoft Excel 数式の一括変更・一括反映(という表現が正しいのかわかりませんが・・・)について質問させていただきます。
お分かりになる方いらっしゃいましたら、ご教示のほどよろしくお願い致します。
使用しているのはOffice 2003(パーソナル)です。
言葉ではうまく説明しにくいので、作成した表の例を記入させていただきます。

............................. 1月             2月 ・・・・
     単価  数量  合計金額
品名1 .. (B1) ... (C1) ...... (D1) .......(...続きを読む

Aベストアンサー

こんにちは!
質問内容を取り間違えていたらごめんなさい・・・

1月の合計(例えばA1+A3+A5)の数式を変えた場合(A2+A4のように)に
2月~12月すべての合計の数式を同じ行配列にしたい!
という判断で回答させていただきます。

関数では出来るか出来ないか判りませんが、(VBAなら出来るかも・・)
一つの方法としてマクロに記録してみてはどうでしょうか?

まず、1月の合計欄にとりあえず A2+A4 のように一つ数式を入れておきます。
この次からマクロに記録します。
ツール → マクロ → 新しいマクロの記録 を選択して

先ほどの1月の合計が入っているセルをクリック → コピー → 
2月の合計セルをクリック → 形式を選択して貼り付け → 数式を選択 → OK
同じように3月~12月まで同じ操作を繰り返します。
ここでマクロの記録を終了します。

これで何とか希望に近いものが出来ないでしょうか?

尚、1月の合計数式が変わった場合は1月のみの数式を入れて、
ツール → マクロ → マクロ → 実行
これで12月までが一気に変わると思います。

以上、参考になれば幸いですが、
的外れの回答なら読み流してください。m(__)m

こんにちは!
質問内容を取り間違えていたらごめんなさい・・・

1月の合計(例えばA1+A3+A5)の数式を変えた場合(A2+A4のように)に
2月~12月すべての合計の数式を同じ行配列にしたい!
という判断で回答させていただきます。

関数では出来るか出来ないか判りませんが、(VBAなら出来るかも・・)
一つの方法としてマクロに記録してみてはどうでしょうか?

まず、1月の合計欄にとりあえず A2+A4 のように一つ数式を入れておきます。
この次からマクロに記録します。
ツール → マクロ → 新し...続きを読む

Q複数の文字列のいずれかが含まれていたらTRUEを返す関数について

複数の文字列のいずれかが含まれていたらTRUEを返す関数について
エクセル2003を使用しています。
B列に例えば住所が入力されていて、「東京都北区」「千代田区」「東村山市」等いくつかの文字列のいずれかが含まれていたら、A列にTRUEを返すということをしたいのですが方法がわかりません。
単純なことのような気がするのですがどうしても思いつきません。よろしくお願いいたします。

Aベストアンサー

こうかなあ。。。
A2セル
=OR(SUBSTITUTE(B2,$D$2:$D$4,"",1)<>B2)
[Ctrl]+[Shft] +[Enter] で確定、配列数式です({}で囲まれる)
下へオートフィル

D2:D4に文字列を入力しておく

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m


人気Q&Aランキング

おすすめ情報