Excel（エクセル）活用コミュのエクセル関数の質問です

こばこばと申します。
仕事は経理でエクセルを使って振替伝票の入力をしています。
先日、上司から次のような指示を受けました。
「振替伝票の金額欄に連動性をもたせるようにしろ」とのことでした。

具体的に言います。

Ａ1のセルに1、Ａ２のセルに1、Ａ３のセルに２、Ｂ1のセルに２，０００
Ｂ２のセルに３，０００、Ｂ３のセルに４，０００と入力されています。

別のシートに振替伝票のフォーマットが２つあります。?と?とします。
Ａ1が1だとＢ1の２，０００が?の振替伝票の金額欄に連動されます。Ａ1が２だと?の振替伝票の金額欄に連動されます。
振替伝票の金額欄は６個あります。Ａ1からＡ３まで全て1なら?の振替伝票の金額欄は上から順に２，０００、３，０００、４，０００となります。
Ａ1が1でＡ２とＡ３が２なら?の振替伝票は２，０００、
?の振替伝票は３，０００と４，０００になります。
金額欄は上から順に埋めていくので、一番上の空白セルを探して連動性を持たせなくてはいけません。

ただ一番上の空白セルを捜して連動性をもたせる方法が全く分かりません。

上司からはＩＦ関数とＬＯＯＫＵＰ関数を使うとできるよとヒントをもらいましたが分かりません。

ご存知の方がいらっしゃいましたら教えてください。

コメント(19)

最初
全て
最新の40件

[1] mixiユーザー 07月25日 01:53

何日も経っているのにレスが付いていませんね。
質問内容に不備があるのではないでしょうか。

> 別のシートに振替伝票のフォーマットが２つあります。
> (1)と(2)とします。
> Ａ1が1だとＢ1の２，０００が(1)の振替伝票の金額欄に連動されます。
> Ａ1が２だと(2)の振替伝票の金額欄に連動されます。
> 振替伝票の金額欄は６個あります。
> Ａ1からＡ３まで全て1なら(1)の振替伝票の金額欄は上から順に２，０００、３，０００、４，０００となります。
> Ａ1が1でＡ２とＡ３が２なら(1)の振替伝票は２，０００、 (1)の振替伝票は３，０００と４，０００になります。

具体的に言います。とありますが、具体的には書かれていません。

あるワークシートのA1セルの値が 1 で隣のB1セルが 2000 であったら、
A1セルに対応する(1)側のワークシートの概要箇所に 2000 と表示させたい。
あるワークシートのA1セルの値が 1 で隣のB1セルが 3000 であったら、
A2セルに対応する(1)側のワークシートの概要箇所に 3000 と表示させたい。
あるワークシートのA1セルの値が 1 で隣のB1セルが 4000 であったら、
A3セルに対応する(1)側のワークシートの概要箇所に 4000 と表示させたい。

としたいことは、確信は持てませんが(具体的ではないので)なんとかくそう思えます。

が、突然出てきた
> 金額欄は上から順に埋めていくので、
> 一番上の空白セルを探して連動性を持たせなくてはいけません。
はサッパリ意味がわりません。
「一番上の空白セルを探して」とは？
「連動性」とは？

ですので、
> ただ一番上の空白セルを捜して連動性をもたせる方法が全く分かりません。
の意味もわかりません。

どのような状況下で何がしたいのか具体的に説明していただけますか。

[2] mixiユーザー 07月25日 02:22

>こばこばさん
■トピック作成時のお約束
・タイトルは具体的かつ簡潔に
　×悪い例『教えてください』
　○良い例『大量のデータの中で不必要な行を削除したい』

また、丸文字は環境によっては文字化けする恐れがあるので
使わないほうが宜しいと思います

[3] mixiユーザー 07月25日 02:35

LOOKUP関数の第2引数である検査範囲または配列は
コード順の昇順に配置されている必要があります
そうでない場合、正しい値を見つけることができない可能性がありますが
その辺りは大丈夫なのでしょうか？？

[5] mixiユーザー 07月28日 17:54

コメントが無いようですが
解決されたのでしょうか？？

[6] mixiユーザー 07月29日 01:30

こばこばさん＞
こんなイメージでしょうか？
＃かなり想像を働かせてます…

＜前提条件＞
・シートは「データ」と「振替伝票1」と「振替伝票2」（全て仮名）
・「データ」のB1=2000、B2=3000、B3=4000
・「データ」のA列はA1からA3の3つのセルに、それぞれ1か2が入る
・「データ」のA列の値が1の行は、値が1の行のB列の値を「振替伝票1」の金額欄に表示
・「データ」のA列の値が2の行は、値が2の行のB列の値を「振替伝票2」の金額欄に表示
・金額欄とは、各振替伝票（1～2）のA1からA3の3つのセル（2シートで計6こ）
・金額欄は上詰めで表示される
例）A1=1 A2=2 A3=1の場合
「振替伝票1」のA1=2000、A2=4000
「振替伝票2」のA1=3000

[7] mixiユーザー 07月29日 08:07

よっちゃんさん、マリ男さん、SMYさんへ
コメントが遅くなって大変申し訳ありませんでした。

SMYさんのイメージ通りです。

こんなことは関数を使ってできるのでしょうか？

[8] mixiユーザー 07月29日 08:21

A列とB列の間に作業列を挿入し、COUNTIFで、
A列が1の場合の連番と、2の場合の連番を入れて、
VLOOKUPで参照すれば出来ます。

[9] mixiユーザー 07月29日 09:54

>7
おそらく、たつや.comさんが仰られている方法が良いと思います

仕事ということでVBAを使える仕事(環境)であれば
ユーザー定義関数を使用する方法もありますが

[10] mixiユーザー 07月30日 07:49

簡単バージョン。詰めは甘いです。
(A列は2の下に1が無い事が前提です。)

　A　B
1 1 2000　
2 1 3000
3 2 4000
4 2 5000
5 2 6000
6 2 7000

D1 =IF(A1=1,B1,"")
D1:D6はD1をコピペ。

F1 {=IF(ISNA(MATCH(1,($A$1:$A$6=2)*1,0)),"",IF(OFFSET(A1,MATCH(1,($A$1:$A$6=2)*1,0)-1,1)="","",OFFSET(A1,MATCH(1,($A$1:$A$6=2)*1,0)-1,1)))}
(F1に{}内の式を入力してCtrlとShiftを押しながらEnterで確定)

F2:F6はF1をコピペ。

難しいバージョン ^^

D1 {=IF(1/LARGE(1/(($A$1:$A$12=1)*ROW($A$1:$A$12)-1/2),ROW())+1/2=0,"",OFFSET(B$1,1/LARGE(1/(($A$1:$A$12=1)*ROW($A$1:$A$12)-1/2),ROW())+1/2-1,0))}

F1 {=IF(1/LARGE(1/(($A$1:$A$12=2)*ROW($A$1:$A$12)-1/2),ROW())+1/2=0,"",OFFSET(B$1,1/LARGE(1/(($A$1:$A$12=2)*ROW($A$1:$A$12)-1/2),ROW())+1/2-1,0))}

D2:D6はD1をコピペ。
F2:F6はF1をコピペ。

[11] mixiユーザー 07月30日 22:38

8通りなら、こんなの作ってVLOOKUPしてしまうのもありかもしれないですね。

＃たつや.comさんの意図が、私は理解しきれなかったのですが、こんなイメージなのでしょうか？

[12] mixiユーザー 07月30日 23:10

>8でたつや.comさんが言ってるのは、こんなイメージでは？

[14] mixiユーザー 07月31日 00:19

コメント10の難しいバージョンについて。
説明は苦手なので少しずつ式を組み立てていきました。

C1:C12 {=(A1:A12=1)*1}

D1:D12 {=(A1:A12=1)*ROW(A1:A12)}

E1 {=SMALL(($A$1:$A$12=1)*ROW($A$1:$A$12),ROW())}
E2:E12はE1をコピペ。

F1 {=1/LARGE(1/(($A$1:$A$12=1)*ROW($A$1:$A$12)-1/2),ROW())+1/2}
F2:F12はF1をコピペ。

G1 {=OFFSET(B$1,1/LARGE(1/(($A$1:$A$12=1)*ROW($A$1:$A$12)-1/2),ROW())+1/2-1,0)}
G2:G12はG1をコピペ。

H1 {=IF(1/LARGE(1/(($A$1:$A$12=1)*ROW($A$1:$A$12)-1/2),ROW())+1/2=0,"",OFFSET(B$1,1/LARGE(1/(($A$1:$A$12=1)*ROW($A$1:$A$12)-1/2),ROW())+1/2-1,0))}
H2:H12はH1をコピペ。

[17] mixiユーザー 07月31日 21:15

13,16は、間違えたので削除

A1=1
A2=2
A3=2
A4=1

B1=2,000
B2=3,000
B3=4,000
B4=5,000

とした場合、C列を作業列として、

C1=A1*1000+COUNTIF($A$1:A1,A1)

C1からC6までコピー

E1=1

F1=2

E2=IF(COUNTIF($C$1:$C$6,E$1*1000+ROW(A1))<1,"",OFFSET($B$1,MATCH(E$1*1000+ROW(A1),$C$1:$C$6,0)-1,))

E2からF6までコピー

[18] mixiユーザー 08月01日 08:17

コメントが遅くなり申し訳ありません。
たつや.comさん、マリ男さん、ゆはえさん、SMYさん、Kusakabeさん
たくさんのコメント本当にありがとうございます。

皆さんのコメントを見てエクセル関数の奥の深さを感じています。
さっそく試してみたいと思います。

[19] mixiユーザー 09月22日 21:28

コメ14自己レス(自分向けの返信はできないらしい)

２年前まで印刷もできていた関数式、今年使おうとしたらなぜかエラー用表示になってました(エクセルと無料オフィスを行き来したのが原因かも)。関数式はそのまま残っていたけど解析する気力も時間もなかったので別の方法で解決しました(集計せずに進めました)。

先日、特に意味もなく「自分の名前 mixi excel」でググっていくつか古いスレを眺めていたら同様の式の自己解説をここに発見、作業列を使って求める結果を表示できました。来年は有効活用できそうです(集計ならフィルター機能を使えって言われそうですがｗ)。

ログインすると、残り4件のコメントが見れるよ