人に聞けない痔の悩み、これでスッキリ >>

下記のような表から、A列のコードをもとにして「D」列の「3」行目の100、200、300(ピンク色)のセルを参照したいのですが関数がわかりません。
別のシートに10000なら100、20000なら200と表示させたいです。
vlookupでは行が1行でないとできませんでした。

「エクセルのデータ抽出方法を教えてください」の質問画像

A 回答 (3件)

以下でいかがですか。


H2 =INDEX(E2:E13,MATCH(G2,A2:A13,0)+1)
「エクセルのデータ抽出方法を教えてください」の回答画像1
    • good
    • 0

こんにちは!



お示しの画像のように結合セルになっている列があるのですね。
そして表示するのはA列に対応するD列の2行目!だとすると・・・

すでにINDEX関数を使う回答は出ていますので、別案として
Sheet1のD列は「数値」だという前提です。

↓の画像でSheet2のB2セルに
=SUMIF(Sheet1!A$2:A$1000,A2,Sheet1!E$3:E$1001)

という数式を入れフィルハンドルで下へコピーしています。
(条件範囲と合計範囲を1行ずらします)

※ エラー処理はしていません。m(_ _)m
「エクセルのデータ抽出方法を教えてください」の回答画像3
    • good
    • 0

どのようなロジックで、A列の数字からD列までを導くのですか?

    • good
    • 0

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

このQ&Aを見た人はこんなQ&Aも見ています

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

Qエクセル リストと完全一致するセルに色をつける

シート1のA列とB列に
aaa ccc
bbb ggg
ccc kkk
ddd ooo
と言うリストがあって、A1〜A4はAチーム、B1〜B4まではBチームと名前を付けています
シート2にAチームのリスト4個が続いているものがあればセルを赤、Bチームのリスト4個が続いているものがあればセルを黄色に塗りたいです
AチームとBチームの中には同じ品番がある時もあります
条件付き書式で設定は出来るでしょうか?

Aベストアンサー

(´・ω・`)
”○” の数を数えるんじゃないんだよなあ。

・・・本題・・・

条件付き書式ですよね。

シート2のリストの並び順は
 aaa
 ccc
 bbb
 ddd
では「Aチーム」と認識しないという事でよろしいでしょうか?
ならば、とても簡単です。

シート2の一覧において、

 判定するセル1
 判定するセル2
 判定するセル3
 色を付けるセル
 判定するセル4
 判定するセル5
 判定するセル6

という範囲について調べれば良いという事。

 判定するセル1
 判定するセル2
 判定するセル3
 色を付けるセル

 判定するセル2
 判定するセル3
 色を付けるセル
 判定するセル4

 判定するセル3
 色を付けるセル
 判定するセル4
 判定するセル5

 色を付けるセル
 判定するセル4
 判定するセル5
 判定するセル6

の4パターンについてそれぞれ調べれば良いだけ。

自分なら
 aaa-bbb-ccc-ddd
のようにシート1から文字列を作り、それが調べるセルで同じパターンになるかを調べます。
シート1はA5セルから、シート2はA11セルからデータが入力されているなら、

 シート1!A5 & シート1!A6 & シート1!A7 & シート1!A8 = A11 & A12 & A13 & A14
 シート1!A5 & シート1!A6 & シート1!A7 & シート1!A8 = A12 & A13 & A14 & A15
 シート1!A5 & シート1!A6 & シート1!A7 & シート1!A8 = A13 & A14 & A15 & A16
 シート1!A5 & シート1!A6 & シート1!A7 & シート1!A8 = A14 & A15 & A16 & A17

という条件になる。
この4つのうちの一つでも条件を満たせばセルに赤色を付ければいい。
「Bチーム」についても同様にすればいいので、
この場合、8つの条件式を設定することになります。

面倒でもこの考え方ができていないと、ちょっと条件が変わっただけで対処できずに終わります。
冒頭で「並び順」について書きましたが、並び順がシート1のリストの通りでなくとも色を付けたい場合でも、この考え方は必要ということです。

・・・
ちなみに厄介なのが、どちらのチームにも「ccc」がいるというところかな。
これが無ければ違う方法でシンプルにできるんですけどねえ。

(´・ω・`)
”○” の数を数えるんじゃないんだよなあ。

・・・本題・・・

条件付き書式ですよね。

シート2のリストの並び順は
 aaa
 ccc
 bbb
 ddd
では「Aチーム」と認識しないという事でよろしいでしょうか?
ならば、とても簡単です。

シート2の一覧において、

 判定するセル1
 判定するセル2
 判定するセル3
 色を付けるセル
 判定するセル4
 判定するセル5
 判定するセル6

という範囲について調べれば良いという事。

 判定するセル1
 判定するセル2
 判定するセル3
 色を付け...続きを読む

Q【関数】複数条件に応じてポイントをつけたい

お世話になっております。

条件によってポイント付与する数式を組みたいのですが
条件が複雑で、どのような関数を組めば良いか、関数の知識も乏しく頭を悩ませております。
詳しい方教えて頂けないでしょうか?

K列 担当者ID
L列 計画
M列 実績
N列 達成率
O列 達成額/未達額
P列 ポイント付与欄

上記のようなデータがあります。

下記条件で、O列「達成額/未達額」の降順にポイントを付与したいです。
その際、下記の条件でポイントを付与したいのです。

ポイント集計先
①黄色セル K5~K18:担当者ID
➁赤色セル K4:部門コード
※画像添付いたします。

ポイント付与条件別表
黄色セル E列:担当者ID ※上記①と紐づく
ピンクセル F列:担当者に紐づく主担当部門コード ※上記➁と紐づく
※補足へ画像添付いたします。

★条件★
・基本
O列
①「達成額/未達成額」が0以上なら降順に10Pからポイント付与
➁「達成額/未達成額」が0orマイナスならポイント付与せず
※ただし、マイナスでも実績があればポイント付与

上記、基本条件に加えて、下記条件も組み込みたいです。
①担当者ID+主担当部門コードが紐づけはO列に10PからポイントMAX付与
➁担当者ID+主担当部門コード以外だったら該当ポイントの1/2付与
かつ、「達成額/未達成額」が0orマイナスなら1/2のポイントの半分付与

何卒、よろしくお願いいたします。

お世話になっております。

条件によってポイント付与する数式を組みたいのですが
条件が複雑で、どのような関数を組めば良いか、関数の知識も乏しく頭を悩ませております。
詳しい方教えて頂けないでしょうか?

K列 担当者ID
L列 計画
M列 実績
N列 達成率
O列 達成額/未達額
P列 ポイント付与欄

上記のようなデータがあります。

下記条件で、O列「達成額/未達額」の降順にポイントを付与したいです。
その際、下記の条件でポイントを付与したいのです。

ポイント集計先
①黄色セル...続きを読む

Aベストアンサー

まず基本条件の方からいきます。
1.セルP5に 「 =IF($M5>0,MAX(10+1-RANK.AVG($O5,IF($M$5:$M$1000>0,$O$5:$O$1000,"")),0),"") 」を入力します。
2.必要なだけ下方向にコピーします。(完了)

*RANK.AVG関数で”達成額/未達成額”の順にランキング(1~)をつけ、11から差し引く、という方法をとります
*”実績”がマイナスのものはランキング付けそのものから除外するので、IF関数で検索対象の配列を絞り込みます
(IF関数に配列を入れたら答えも配列で返してくれるので、その絞り込んだ配列に対してRANK.AVR関数を使います。)
*マイナスのポイントは排除したいので、MAX関数でゼロと比較して正の値のみを採択します

追加条件込みの方は、以下です。
1.セルP5に 「 =IF($M5>0,MAX(10+1-RANK.AVG($O5,IF($M$5:$M$1000>0,$O$5:$O$1000,"")),0)*IF(VLOOKUP($K5,$E$3:$F$1000,2,FALSE)=$K$4,1,IF($O5>0,0.5,0.25)),"") 」を入力します。
2.必要なだけ下方向にコピーします。(完了)

*担当IDから担当部門コードの検索にはVLOOK関数を使います。VLOOK関数が正常に動作するには、担当IDに漏れがない事と番号順に並んでいる事が必要ですので、これ前提で考えて下さい。
*追加条件に準じ、ポイントを1倍したり、0.5倍したり、0.25倍したり、しました。

まず基本条件の方からいきます。
1.セルP5に 「 =IF($M5>0,MAX(10+1-RANK.AVG($O5,IF($M$5:$M$1000>0,$O$5:$O$1000,"")),0),"") 」を入力します。
2.必要なだけ下方向にコピーします。(完了)

*RANK.AVG関数で”達成額/未達成額”の順にランキング(1~)をつけ、11から差し引く、という方法をとります
*”実績”がマイナスのものはランキング付けそのものから除外するので、IF関数で検索対象の配列を絞り込みます
(IF関数に配列を入れたら答えも配列で返してくれるので、その絞り込んだ配列に対してRAN...続きを読む

QExcelで「令和」と表示されるのは5月1日にならないとだめですか?

「日本の新元号に関する Office の更新プログラム」というページ(下記)で、
「Windows と Office の更新プログラムを適用済みの場合でも、Windows 上で実行されている Office 製品は 2019 年 5 月 1 日に新元号が開始されるまで、新元号を表示しませんのでご注意ください。」
と書かれています。
https://support.microsoft.com/ja-jp/help/4478844/office-updates-for-new-japanese-era

今月4月中に、Excelのセルに来月5月以降の年月日を入力した場合に、自動で「令和」という元号を表示させることはできないのでしょうか。

もし、できるということであれば、「2019 年 5 月 1 日に新元号が開始されるまで、新元号を表示しません」とはどのような意味なのでしょうか。

Aベストアンサー

>こちらでは、「4月17日以降にOfficeも更新されれば「令和元年」と表示されると思います」と書かれているんですが

その方は、Microsoftの方ではないですし個人の予想ですよね?公式が出ているのにそれを持ち出してどうするんですか?

5/1より前に新しい元号を表示したい場合は数式や表示形式で限定的に表示させる方法を色々な方が考え付いていますよ。
検索すればたくさん出てきます。

Q4142から653347と飛んでます。 これを4143からちゃんと並ぶにはどうすればいいでしょうか?

4142から653347と飛んでます。
これを4143からちゃんと並ぶにはどうすればいいでしょうか?

Aベストアンサー

これ、9から70に飛んでるよね。

①シート全体を選択
②どの行でもいいので右クリック
③再表示を選択

開いたかな?

Qexcelど素人です。 A列 B列 C列 D列 2 2 3 0 5 2 7 1 11 2 13 1

excelど素人です。

A列 B列 C列 D列
2 2
3 0
5 2
7 1
11 2
13 1

上で示したように、A列に表示されている素数を3で割ったとき、余りが0ならB列に、1ならC列に、2ならD列に表示されるような表を作りたいです。
何とかA列に素数を表示することはできましたが、それからは全く何をしたらよいか分かりません。どなたか教えて頂けませんか?

Aベストアンサー

MOD関数で余りを出し、それをIF関数で各列それぞれ余りが○なら表示、それ以外は空欄というように式を作ればいいかと。

Qエクセル ifの使い方を教えて

商品の受注入力をエクセルで管理したいのですが、商品には1商品1単価のものと、1商品5単価のものが存在します。商品・価格一覧は以下の通りです。実際の表は500行ほどあります。
(商品・価格一覧表)
code size P① P② P③ P④ P⑤
 1 S 20
 5 LL 1000 900 800 700 600

(受注枠)
code  数 size  -- P--  total
 1  5  S  20  *1  100
 5  3  LL  800  *2  2400

受注枠には、商品となるcode番号と 受注数量を入力すると一覧表からsizeと単価が引けるように作りました。
code5の商品は、注文する枚数に応じて、1枚なら1000円、2枚900円、3枚800円、4枚700円、5枚なら600円となるように設定したいと考えています。
受注枠のPの枠セル(*1)(*2)には、以下の式を作りました。

IF(K5="","",IF(J5<=3,VLOOKUP(J5,$B$4:$H$5,3),IF(AND(J5>=4,K5=1),VLOOKUP(J5,$B$4:$H$5,3),IF(AND(J5>=4,K5=2),VLOOKUP(J5,$B$4:$H$5,4),IF(AND(J5>=4,K5>=3),VLOOKUP(J5,$B$4:$H$5,5),IF(AND(J5>=4,K5>=5),VLOOKUP(J5,$B$4:$H$5,6),IF(AND(J5>=4,K5>=5),VLOOKUP(J5,$B$4:$H$5,7))))))))

code1の商品のように単価が1つのものは受注数がいくつでも対応しますが、code2の商品のように複数の単価を持つ商品は、上の式では3列目のP ③の800までしか対応してくれません。
受注枠の数量欄に 4 と入力しても5と入力しても800と表示されるだけです。

参考書によると、ifの使い方に問題があるのかと思いますがどうしても解決しません。
参考書片手に作成しましたがこの問題を解決していただけないかと質問に投稿させていただきました。
何卒解決の道筋を教えていただきたくお力をお借りします。宜しくお願い致します。

商品の受注入力をエクセルで管理したいのですが、商品には1商品1単価のものと、1商品5単価のものが存在します。商品・価格一覧は以下の通りです。実際の表は500行ほどあります。
(商品・価格一覧表)
code size P① P② P③ P④ P⑤
 1 S 20
 5 LL 1000 900 800 700 600

(受注枠)
code  数 size  -- P--  total
 1  5  S  20  *1  100
 5  3  LL  800  *2  2400

受注枠には、商品となるcode番号と 受注数量を入力すると一覧表からsizeと単価が引けるように...続きを読む

Aベストアンサー

よく分からないのですが、こんなので参考になりますか。
K2セルに次の式が入っています。

【K2セル】=HLOOKUP(J2,A:G,MATCH(I2,A:A,0),TRUE)

QIF関数とMODで、例えば入れた数値の倍数の時に◯が表示される……という数式を作りましたが、1.1の

IF関数とMODで、例えば入れた数値の倍数の時に◯が表示される……という数式を作りましたが、1.1の時に本当だったら1.1、2.2、3.3、4.4、5.5、6.6、7.7……で◯になるはずなのですが、10以下の数値を入れてみると、3.3、5.5、6.6、7.7、9.9は◯の表示がでてきません。
1.1、2.2、4.4、8.8しか◯が出ません。
どうしてでしょうか?

Aベストアンサー

幼稚な言い方だけど、Excel(に限らないので、PC全般的なことと理解してネ)は“小数点の計算には弱い”と理解しておきませう。
理屈っぽい人はフドーショースーテン(浮動小数点)問題などど言い始めますが・・・
それはさておき、対策を教えます。カンタンなことで、小数点抜きの整数に換算して、Excel に計算させることです。
貴方が提示した式(実はナッチョランでしたが)、を
=IF(MOD($A2*10,B$1*10)=0,"◯","")
に変更するだけで解決します。ゴチャゴチャ考えずに先ずは実行してみること!
どうなりましたか?

Q保存先フォルダとファイル名について

いつもお世話になっております。
保存をかける際にダイアログボックスを出したく、
またシートA1には保存先、B1にはファイル名を指定しておきたいのですが
どのようにすればよろしいでしょうか?
色々調べたのですが解決できず、アドレスどうぞよろしくお願いします。

ちなみにEXCEL2013を使用しています。

Sub CsvExportWithQuotation()
 Dim FileName As Variant
 Dim Rng As Range
 Dim LastCell As Range
 Dim c As Range
 Dim i As Long
 Dim strLine As String, fname As String, fpath As String

fpath = cells(1,1).value
fname = cells(1,2).value
 FileName = Application.GetSaveAsFilename( fpath & "¥" & fname,fileFilter:="CSVt Files (*.csv), *.csv")

保存先は指定出来るのですが、ファイル名が表示されません。
何卒よろしくお願い致します。

いつもお世話になっております。
保存をかける際にダイアログボックスを出したく、
またシートA1には保存先、B1にはファイル名を指定しておきたいのですが
どのようにすればよろしいでしょうか?
色々調べたのですが解決できず、アドレスどうぞよろしくお願いします。

ちなみにEXCEL2013を使用しています。

Sub CsvExportWithQuotation()
 Dim FileName As Variant
 Dim Rng As Range
 Dim LastCell As Range
 Dim c As Range
 Dim i As Long
 Dim strLine As String, fname As String, fp...続きを読む

Aベストアンサー

No1です。

>セルではなく直接書いてみたのですが
>ファイル名はFALSE.csv と表示がされました。
ご提示の通りの式を与えれば、そうなります。

第一引数をファイル名と解釈して評価しようとしますので、
> InitialFilename = "保存先&ファイル名"
 1)まず式をそのまま評価すると False(論理値)となり
 2)要求されているのは文字列なので、変換した"FALSE"を値として採用
 3)拡張子「.csv」が付け加えられて
 4)ダイアログのファイル名欄に「FALSE.csv」と表示
という処理がなされているものと思います。

>セルに関数も入っていないのに謎です
セルの値を参照していないのであれば、セルの状態がどうであるかは関係ないはずです。
(関係したら、その方がおかしい)

Qエクセルの一覧からカードを作成したい

同様の質問を見つけてはいるのですが、なかなか理解できず…
教えていただきたいです。
まずはじめに、このエクセルは前任者が作成しており、
どのような計算式を使っているのかも不明、という状況です。

エクセルのシート1(data)に一覧が入るようになっています。
A列=日付、B=名称、C=部署…N列まであります。
※別シートのボタンを押して、別で用意してあるCSVファイルを選択する
とデータが入るようになっています。

エクセルシート2にカードが作成されます。
※計算式が入っていたので、表示させた画像を添付しました。

現在は12枚のカードが作成されるようになっていて、このサイズでA4に収まります。
これを90人分作成の依頼があり、行をコピーしても、列をコピーしてもうまく反映されません。
一つずつ計算式の参照を変更するには時間がかかってしまいます。

何とかお知恵をお貸しください。
よろしくお願いいたします。

Aベストアンサー

こんにちは

まったく、No1様のおっしゃる通りですね。


とは言え、もう引き下がれないのなら、知恵を使ってできる範囲で対応するのも社会人の資質だとも思いますけれど・・・

>これを90人分作成の依頼があり
目的は、一覧リストからある形式に変換されたラベル状のものを90人分作成するということですよね。
しかも、最初の12人分はすでに作成できる状態であるってことでしょ?
(仕組みとしてセル参照の関数になっていることまでわかっている)
『90人分に対応できる仕組みを作る』という依頼ではないのですよね?

ならば簡単。
まず現状の表示を別シートに1ページの内容を「コピー」→「値をペースト」で保管
一覧データの13~24人目を1~12のセルへコピペ
対応するラベルができるので、これをコピー → 先ほどの下方に、値のペースト

上記を8回繰り返せば終わるので、悩んでいる間の、ものの10分もあれば足りると思いますが。


>行をコピーしても、列をコピーしてもうまく反映されません。
関数をコピーしてうまく参照関係を移動する方法の一つとして、セルの位置(行や列)から計算して、OFFSETやINDEX<、INDIRECT関数などを利用して目的の参照にするものがあります。
今後とも表計算ソフトを利用なさるのなら、お時間のある時に覚えておいても損はないかも知れません。

あるいは、VBAを覚えておくといろいろなことができるようにはなりますが、若干ハードルが上がるのと、向き不向きがあるので誰でもというわけにはいかないです。

こんにちは

まったく、No1様のおっしゃる通りですね。


とは言え、もう引き下がれないのなら、知恵を使ってできる範囲で対応するのも社会人の資質だとも思いますけれど・・・

>これを90人分作成の依頼があり
目的は、一覧リストからある形式に変換されたラベル状のものを90人分作成するということですよね。
しかも、最初の12人分はすでに作成できる状態であるってことでしょ?
(仕組みとしてセル参照の関数になっていることまでわかっている)
『90人分に対応できる仕組みを作る』という依頼ではないのです...続きを読む

Qエクセルのファイルを開く際の質問です

エクセルのファイルを開くと、常にAの160のセルが左上に表示させるような方法はありませんでしょうか、みんなが使うので、いつも開くと、最期のほうのページが表示されます。
VBAになるのかと思いますが、思いつきません。
先頭行の固定は必要なのですが、なんとか途中のページにある文章を左上にいつも表示させて開きたいのです。どなたか、お救い下さい。

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

Aベストアンサー

こんばんは。

'//Thisworkbook モジュールに貼り付けます。
Private Sub Workbook_Open()
  Application.Goto Worksheets(2).Range("A160"), True
End Sub

マクロ付きのブック(xlsm)か、バイナリのブック(xlsb) で保存します。

ただし、もし、拡張子が、xlsx でマクロなしのブックを指定した場合は、個人用マクロブックなどを使った特別のブログラムが必要になります。それはまた、ご相談ください。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング