エクセル2003に詳しい方御教授願います。

質問

A　　　　B　　　　C　　　　D　　　　E　　　　F　　　　G　　　　H　　　　I
1　　　　　　　　　　　　SA1　　　SA2　　SB1　　SB2　　　SC1　　SC2　　SC3
2　　番号　　名前
3　　111　　　Xさん　　○　　　　　　　　　○　　　　　　　　　　　　　　　　　　○
4　　222　　　Yさん　　　　　　　　○　　　　　　　　　　　　　　　　　　○

上記のような情報がエクセルに記入されていたとします。
A2,B2,C1,D1,E1,F1,G1,H1,I1にそれぞれ項目名(列名)があるとします。
番号111と222の人がC1～I1までの項目に対して上記のように○が付いているとき
下記のようなSQL文(5行)をA5～A9に自動発生させることを実現したいです。

update tablename set type = SA1 where user = qqq111 ;
update tablename set type = SB1 where user = aaa111 ;
update tablename set type = SC3 where user = 111 ;
update tablename set type = SA2 where user = qqq222 ;
update tablename set type = SC2 where user = 222 ;

上記５文はそれぞれ左から6項目10項目が可変です。
10項目は、
SAのどれかに○が付いている人は、qqq+番号
SBのどれかに○が付いている人は、aaa+番号
SCのどれかに○が付いている人は、番号
という法則で可変します。

エクセルはまだまだ初心者でして、vbaを使うしかないのかそれとも
何か標準の関数を使えば解決するのかすら検討できません。

以上長くなりますが、何卒宜しく御教授お願い致します。

回答No.2

keithin · ベストアンサー

ＶＢＡを知りたいご相談じゃなかったんですか。

手順：
ブックを開く
ＡＬＴ＋Ｆ１１を押す
挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピー貼り付ける

sub macro1()
　dim c as range
　dim c0 as string
　dim n as long
　dim s1 as string, s2 as string, s3 as string

　with range("C:I")
　set c = .find(what:="○", lookin:=xlvalues, lookat:=xlwhole)
　if c is nothing then exit sub

　c0 = c.address
　n = 5
　range("A5:A65536").clearcontents

　do
　　s1 = cells(c.row, "A").value　’number
　　s2 = cells(1, c.column).value　’SA1 - SC3

　　select case left(s2, 2)　’別にIf Thenでも全然ＯＫです
　　case "SA"
　　　s3 = "qqq"
　　case "SB"
　　　s3 = "aaa"
　　case else
　　　s3 = ""
　　end select

　　cells(n, "A") = "update tablename set type = " & s2 & " where user = " & s3 & s1 & " ;"
　　set c = .findnext(c)
　　n = n + 1
　loop until c.address = c0
　end with
end sub

ファイルメニューから終了してエクセルに戻る
データを記入してあるシートを開く
ＡＬＴ＋Ｆ８を押してマクロを実行する。

＃まったく二度手間になっちゃいました。

補足

＞エクセルはまだまだ初心者でして、vbaを使うしかないのかそれとも
＞何か標準の関数を使えば解決するのかすら検討できません。
一応こう書いておいたつもりでしたが。

回答No.4

keithin

関数の方がよかったなら。

とりあえず簡単に
Ｂ５に
=IF(ROW(B1)>COUNTIF($C$3:$I$4,"○"),"",INDEX(A:A,INT(SMALL(IF($C$3:$I$4="○",ROW($C$3:$I$4)*100+COLUMN($C$3:$I$5)),ROW(B1))/100)))
と記入、必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力、以下コピー

Ｃ５に
=IF(ROW(B1)>COUNTIF($C$3:$I$4,"○"),"",INDEX($1:$1,MOD(SMALL(IF($C$3:$I$4="○",ROW($C$3:$I$4)*100+COLUMN($C$3:$I$5)),ROW(C1)),100)))
と記入、必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力、以下コピー

Ａ５に
=IF(B5="","","update tablename set type = "&C5&" where user = "&IF(LEFT(C5,2)="SA","qqq",IF(LEFT(C5,2)="SB","aaa",""))&B5&" ;")
とふつーに記入、以下コピーで出来上がり。

＃勘違いしないでください。あなたがあなたのつもりであるように、こちらは二度手間を踏んだという事実を指摘しているだけです。

お礼

嗚呼そういうことでしたか。
ご回答ありがとうございます。
関数でも確認してみます。

回答No.3

Nouble

まずはヒントから

と、その前に確認です
横並びを行、縦並びを列をします。

一例として
ＳＡ２についての表示を出すヒントですが

ます極々簡単なことから
空白を０、○を１と仮にします

表を見ると、上から順に
空白、○
となっていますから、これを置き換えると
０，１ ですね　　　　　　　　　　　　　・・・(1)

このままでは使えませんから
これに意味を持たせます。

持たせる意味は「何行目か」です。
今回はラベル行からの距離にします。
番号１１１の方は１の距離
番号２２２の方は２の距離
ですね、
距離だけを羅列すると
１，２ですね

これを先程の(1)とかけてみましょう
{０,１}×｛１,２｝={０,２}　　　　　　　・・・(2)

ここまでは良いでしょうか？

本来は、もっともっと件数が在り、
恐らくは、１列に複数箇所○があるのでしょうから、
「０以外の最小の値を探す」
のではなく
「０以外の値を羅列する」
このようにしてみます。

まずは全体の件数を数えます。
今回の場合では２件ですね、

そして０以外の件数を数えます。
今回の場合では１件ですね、

(2)を大きい順に並べます
２，０
０以外だった件数まで、最大から順に取り出します。
今回は1件だけだったので
２
ですね。

表題から距離２、下の位置に該当のものがあると解ります。

さて、
ここまで一切関数の話しを入れて来ませんでしたので
各機能を実現させる関数や式をご紹介します。

まず○や空白の羅列を、０と１に変える方法です。
ｃのＮ列目までデータがあるものとしますと、
=(C3:CN="○")+0
です。

でもこれではデータ件数が増えた場合使いにくいですよね？
範囲内のデータ件数を調べるには、
「対象となるデータが数値かどうか」
などにもよりますが、

データが連続しているものとすれば
COUNTA(データ範囲)
がいいでしょう。

Pcの能力にもよりますが、
最近のPcなら、一列まるまるを指定しても大丈夫かも知れません。

仮にＡ列に何件 データが含まれるか、
これを見ていましょう。

COUNTA($A:$A)-見出しの行数(今回は1)
ですね。

さて、未だ使えません、この情報だけでは…
実際のデータを可変的に、未だ読めていませんからね

可変的なデータの読み出しに使えるものは幾つかあります。
主なものとしては、OFFSET、INDEX、INDIRECT、
等がありますが、

数値を直感的に使える点から、今回はOFFSETを使いたいと思います。
OFFSETは、ヘルプにもある通り、
OFSET(基準位置、基準位置からの下をプラスとした時の距離、基準位置からの右側をプラスとした時の距離、縦方向のセル幅、横方向のセル幅)
と、なっています。

例として、A3:A4を選択するようにしてみます。
仮に機銃位置をA2とします。
A3は縦方向に距離1、横方向に距離0、
A3:A4は縦方向に2の幅、横方向に1の幅ですね、
OFFSETに入れてみます。
OFFSET($a$2,1,0,2,1)
此所で「＄」というものが出てきましたが、
これは、「セルをコピーした時もこの次のセル位置を決める文字を変えない」
と、いう意味です。

今回はA3:A4と
縦幅が決まっていましたからこう書きましたが、
こう書きましたが、
予めでは解らない場合は、先程のCOUNTAを使えば良いでしょう、

こうです
OFFSET($a$2,1,0,COUNTA($A:$A)-1,1)　(※注:今回は見出しが1行あるので1を引きました)

余談ですが、
横方向の位置も戦略的に、可変的に、知りたいですよね？
そういう時はMATCHなどが使えます。

試しにデータ表表題から「SA2」を探してみます、
MATCH(1,INDEX((offset(&B&1,0,1,1,COUNTA($1:$1)),,)="SA2")*1,0)
他にも書き方は色々あるでしょうが今回はこれで、

では、任意サイズのデータ範囲の中から、表見出し"SA2"直下だけのデータ全件を取りだしてみましょう。

何件あるかは先程来と同じCOUNTA($A:%A)-1ですね
今回の基準位置はB2とします。
読出す"SA2"のあるセル群の一番上のデータセルは
縦距離が１，横距離がMATCH(1,INDEX((offset(&B&1,0,1,1,counta($1:$1)),,)="SA2")*1,0)
縦幅がCOUNTA($A:$A)-1、横幅が１、ですね
入れてみます。

OFFSET($B$2,1,MATCH(1,INDEX((offset(&B&1,0,1,1,COUNTA($1:$1)),,)="SA2")*1,0),COUNTA($A:$A)-1,1)
ですね、

これについて
○ならば1，空白なら0にしてくれる先程の式を使えば良い
と、いう訳です。
(OFFSET($B$2,1,MATCH(1,INDEX((offset(&B&1,0,1,1,counta($1:$1)),,)="SA2")*1,0),COUNTA($A:$A)-1,1)="○")*1

所で
「0以外の数を数える」と、云うことは 逆説的に云えば、
「1の個数を数える」と、同じ事。
ならば総和を求めたら良い…
ですよね？

でも問題が1つ
SUM関数が問題があって使えません。
SUM関数には特殊な機能が忍ばせてあるので、
算出させる場所によって値が変動するのです。

なので
こういった場合では信頼性に欠けます。
こういった場合はSUMPRODUCTを使います。

こうですね
SUMPRODUCT((OFFSET($B$2,1,MATCH(1,INDEX((offset(&B&1,0,1,1,counta($1:$1)),,)="SA2")*1,0),COUNTA($A:$A)-1,1)="○")*1)

ところで、
SUMPURODUCTでは1つの数字しか貰えません。
今回は最も大きいものからからｎ個データが欲しいのです、　ね。
最大を1、次を2、その次を…
という時は{1，2，3，…}という数値が欲しいのですが、

こういう時は
ROW(INDIRECT("A1:A"&n))
と、書けばn迄の連続した数値が得られます。

仮にnを5とすると、
ROW(INDIRECT("A1:A"&5))
と、書けば
{1，2，3，4，5}
と、なります。

先程挙げた「大きい順に並べる」は
LARGEが使えます。

例としてはこうです
LARGE(index({2,3,1,5,4,2,6,4,8,2,5,3,9,1},,),{1,2,3,4,5})
取り出される答えは
{9,8,6,5,5}です
大きいもの順に5つ取り出されています。

LARGE(index({2,3,1,5,4,2,6,4,8,2,5,3,9,1},,),{1})
得られるのは{9}、最大から1番目が取り出されています。

LARGE(index({2,3,1,5,4,2,6,4,8,2,5,3,9,1},,),{1,5})
得られるのは{9,5}、最大から1番目と、5番目が取り出されています。

後はこの取り出された0以外の表外からの距離を1つずつ使って
読出す関数OFFSETと組み合わせて
番号を知り
SAやSBの時は各々qqq、aaaをつけて
=CONCATENATE("update tablename set type = ",SA2(なのにデータか)," where user = ",qqq(SAに対する接尾語,読み出した番号)
とすれば良いのですね。

要点は全てさらせているつもりですが、
如何でしょうか？

ヒントにしては云い過ぎましたかね？
後は応用だけです。

お役に立てていたならば幸いです。

お礼

ヒントありがとうございます。
追いながら理解するのに、少々お時間かかりそうですね＾＾；
時間あるときに解読します。
ありがとうございます。

回答No.1

keithin

ふつーに○を探して文字列を作っていくだけです。

sub macro1()
　dim c as range
　dim c0 as string
　dim n as long
　dim s1 as string, s2 as string, s3 as string

　with range("C:I")
　set c = .find(what:="○", lookin:=xlvalues, lookat:=xlwhole)
　if c is nothing then exit sub

　c0 = c.address
　n = 5
　range("A5:A65536").clearcontents

　do
　　s1 = cells(c.row, "A").value　’number
　　s2 = cells(1, c.column).value　’SA1 - SC3

　　select case left(s2, 2)　’別にIf Thenでも全然ＯＫです
　　case "SA"
　　　s3 = "qqq"
　　case "SB"
　　　s3 = "aaa"
　　case else
　　　s3 = ""
　　end select

　　cells(n, "A") = "update tablename set type = " & s2 & " where user = " & s3 & s1 & " ;"
　　set c = .findnext(c)
　　n = n + 1
　loop until c.address = c0
　end with
end sub

補足

回答ありがとうございます。
良かったら実行の仕方の手順も加えて教えていただけますか？
初心者なものでして＾＾；