[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『表数値を用いて計算2』（つぶ）
下記の様な表があります。
A B C D E F G ・・・
1 100 180 230 260 360 ・・・
2 010 015 020 024 031
3 200 013
4 252 018
5 303 03
6 348 032
7 405 04
8 455 043
9 510 05
・ ・
・ ・
・ ・
リストボックス1に、
「○○ 010013」
とあり、
リストボックス2に、
「△△ 02003」
とあったとします。
リストボックス1･2を選択した時に、
仮にA1に、
「○○+△△ 03105」
と表示させたいです。
数値については、
リストボックス1の値で説明すると、
010が横方向の値で、013が縦方向の値です。
この時、010は100であり、013は360です。
これをリストボックス１と２で合せて、
100(010)+230(020)=330≒360(031)
200(013)+303(03)=503≒510(05)
で、「03105」となります。
リストボックスの値をばらして、表からそれに連動する数値を読み取り、
連動する数値を足して、その合計と近似する値を表から読み出して合体させる？
なんてことは出来ますでしょうか？
宜しくお願いします。
＜ 使用 アプリ：Excel2000、使用 OS：WindowsXP ＞
＞リストボックス1に、 ＞ 「○○ 010013」 ＞とあり、 ＞リストボックス2に、 ＞ 「△△ 02003」 ＞とあったとします。
１．その○○ とか △△とは現実には何ですか？
２．「○○+△△」とは具体的にどんなことになるんですか？ 常識的には、○○と△△は加算できないです。
３．02003が、020と03に分かれるようですけど、 なぜ、02と003には分割されないと云えるのですか？ (左から必ず3桁取ると云うことなんですか？）
４.Ｂ列のデータの頭に「0」がついているので、そのデータは文字列なんですね？
（半平太） 2015/10/13(火) 19:14
＞２．「○○+△△」とは具体的にどんなことになるんですか？
常識的には、○○と△△は加算できないです。
→１．の通りです。
＞３．02003が、020と03に分かれるようですけど、
なぜ、02と003には分割されないと云えるのですか？
(左から必ず3桁取ると云うことなんですか？）
→左から必ず3桁とることで大丈夫です。
桁数的には5桁〜6桁ですが、必ず3桁目で分かれます。
＞４.Ｂ列のデータの頭に「0」がついているので、そのデータは文字列なんですね？
→文字列です。
（つぶ） 2015/10/14(水) 08:50
当該シートのシートモジュールに（↓）
Sub crossRef()
Dim App As Application
Dim L1上下, L2上下
Dim L1横Val, L2横Val
Dim L1縦Val, L2縦Val
Dim L1num, L2num
Dim L1横Pos, L2横Pos
Dim L1縦Pos, L2縦Pos
Dim Hrange As Range
Dim Vrange As Range
Dim 横計, 横解
Dim 縦計, 縦解
Set App = Application
Set Hrange = Range("C2", Range("C2").End(xlToRight))
Set Vrange = Range("B3", Range("B3").End(xlDown))
L1上下 = Split(Replace(Me.ListBox1.Value, " ", " "), " ")(0)
L2上下 = Split(Replace(Me.ListBox2.Value, " ", " "), " ")(0)
L1横Val = Left(Split(Replace(Me.ListBox1.Value, " ", " "), " ")(1), 3)
L2横Val = Left(Split(Replace(Me.ListBox2.Value, " ", " "), " ")(1), 3)
L1縦Val = Mid(Split(Replace(Me.ListBox1.Value, " ", " "), " ")(1), 4, 3)
L2縦Val = Mid(Split(Replace(Me.ListBox2.Value, " ", " "), " ")(1), 4, 3)
L1num = App.CountIf(Hrange, Left(L1横Val, 3))
L2num = App.CountIf(Hrange, Left(L2横Val, 3))
If L1num = 0 Or L2num <> 1 Then
Range("A1").Value = "コードが不正です"
Set App = Nothing
Exit Sub
Else
L1横Pos = App.Match(Left(L1横Val, 3), Hrange, 0)
L2横Pos = App.Match(Left(L2横Val, 3), Hrange, 0)
L1縦Pos = App.Match(Left(L1縦Val, 3), Vrange, 0)
L2縦Pos = App.Match(Left(L2縦Val, 3), Vrange, 0)
横計 = Hrange.Offset(-1).Cells(L1横Pos) + Hrange.Offset(-1).Cells(L2横Pos)
縦計 = Vrange.Offset(, -1).Cells(L1縦Pos) + Vrange.Offset(, -1).Cells(L2縦Pos)
横解 = Hrange.Cells(1, App.CountIf(Hrange.Offset(-1), "<" & 横計) + 1)
縦解 = Vrange.Cells(App.CountIf(Vrange.Offset(, -1), "<" & 縦計) + 1, 1)
End If
Range("A1").Value = L1上下 & "+" & L2上下 & " " & 横解 & 縦解
Set App = Nothing
End Sub
（半平太） 2015/10/14(水) 18:22
こちらのご説明不足で本当に申し訳ないのですが、
入力規則のリストボックスでして、上手く動作しないです。
Me.ListBox1.Valueをセル位置にすればよいのでしょうか？
（つぶ） 2015/10/15(木) 09:13
入力規則のリストボックスだとすると、二つも同時に存在できないですから こんな状態にはならないですよね？ ↓ ＞リストボックス1に、 ＞ 「○○ 010013」 ＞とあり、 ＞リストボックス2に、 ＞ 「△△ 02003」 ＞とあったとします。
質問の前提となる状況が理解できませーん。
（半平太） 2015/10/15(木) 09:32
強引に関数で･･･。参照するセルは実際のものと変えてください。
I1セルに ○○ 010013 I2セルに △△ 02003 があるとして
I4セル =MID(I1,1,FIND(" ",I1)-1)
J4セル =MID(I1,FIND(" ",I1)+1,3)
K4セル =SUBSTITUTE(I1,I4&" "&J4,"")
I4セルからK4セルを選択した状態で5行目にフィルコピー
L4セル =INDEX($C$1:$G$1,MATCH(J4,$C$2:$G$2,0)) M4セル =INDEX($A$3:$A$9,MATCH(K4,$B$3:$B$9,0)) L4セルからM4セルを選択した状態で5行目にフィルコピー
L6セル =IF(ISERROR(INDEX(C2:G2,MATCH(L4+L5&"",C1:G1,1)+1)),INDEX(C2:G2,MATCH(L4+L5&"",C1:G1,1)),INDEX(C2:G2,MATCH(L4+L5&"",C1:G1,1)+1)) M6セル =IF(ISERROR(INDEX(B3:B9,MATCH(M4+M5&"",A3:A9,1)+1)),INDEX(B3:B9,MATCH(M4+M5&"",A3:A9,1)),INDEX(B3:B9,MATCH(M4+M5&"",A3:A9,1)+1)) A1セル =CONCATENATE(I4,"+",I5," ",L6,M6) （se_9） 2015/10/15(木) 17:32
まず半平太さん
説明不足ですみません。別々のセルに入力規則のリストボックスを設定してますので、
１つのセルに二つも同時に存在しているわけでは無いです。
仮にI1セルをリストボックス１として、I2セルをリストボックス2としている感じです。
se_9さん
私の下手な説明の意図を理解して下さいありがとうございます。
ご提示頂いた関数が正に希望する動作です。ありがとうございます。
ただ１点だけですが(多分此方の不具合だと思いますが）、
L6セル =IF(ISERROR(INDEX(C2:G2,MATCH(L4+L5&"",C1:G1,1)+1)),INDEX(C2:G2,MATCH(L4+L5&"",C1:G1,1)),INDEX(C2:G2,MATCH(L4+L5&"",C1:G1,1)+1))
のみ、#N/Aとエラーになります。
参照するセルは、表共に式と同じに合せてみたのですが、同様にエラーとなってしまいます。
M6セル=の方は正常に動作する為、違いを確認しましたが、これといって見つけれませんでした。
お手数お掛け致しますが、ご意見頂ければ幸いです。
（つぶ） 2015/10/22(木) 09:32
L4セル、L5セルの数値は右に寄っていますか？もしそうならL6セルの関数の&""を取ってみてください。 A列、B列、1行目、2行目のセルが文字列になっていると思って&""をつけてみたのですが、多分1行目、2行目の セルが標準になっているのでエラー（#N/A）が表示されるのだと思います。 （se_9） 2015/10/23(金) 07:43
申し訳御座いませんが１点お伺いしたいのですが、
リストボックス１＆２の値を合計した数に一番近い値を
表から読み出してると思うのですが、どの様な仕組み？に
なっているのか、簡単でいいのでお教え頂ければ幸いです。
合計値に最も近い値では無く、合計値以上で最も近い値になるのでしょうか？
そこら辺の設定は自由に変えれるのでしょうか？
（つぶ） 2015/10/26(月) 18:02
すみません、説明しようと思ってふと考えたのですが
a 010013 b 010018
の場合、
1.a+b 020043 2.a+b 015043
1と2どちらが正しい答えになりますか？それとも2つともまったく違う答えになってしまうのか･･･。 （se_9） 2015/10/27(火) 16:17
返事が遅くなってすみません。
MATCH関数の使い方 http://excel.onushi.com/function/match.htm
照合の型で1を指定しているので、検査値以下の最大の値を検索するわけですが（例えば179だと010になる） つぶさんの規則性だと179のパターンでは015になるのでMATCH関数で取得された値に+1しています。でも 180だと015を取得しなければいけないかな？と色々考えていたら頭が痛くなってきた･･･。
つぶさんが希望しているような設定もできるかとは思いますが、そのことで質問されても多分私の能力では 解決できなさそうなので、他の回答者に助けを求める可能性大です（無責任ですみません） （se_9） 2015/10/30(金) 12:05
また貴重なお時間頂き感謝致します。
現状、se_9さんがお教え頂いた内容で十分満足行く結果が得られておりますので、
感謝しか感じておりません。全く無責任では無いです。
長々とお付き合い頂きありがとうございました。
（つぶ） 2015/10/30(金) 16:29
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.