エクセルの学校：全文検索：Hyper Estraier：関数

Results of 1 - 1 of about 33541 for 関数 (0.006 sec.)

[[20210202160256]]: #score: 2646; @digest: ef8e0145d814fa08b68e0061ba99c29e; @id: 86570; @mdate: 2021-02-08T04:25:01Z; @size: 15161; @type: text/plain; #keywords: 外長 (54132), 内長 (38555), 梅） (27534), 長合 (26192), dv3 (23579), （梅 (17129), aggregate (11985), 三引 (9931), ・| (9595), ラグ (9478), 減算 (9452), フラ (8865), 電源 (7832), 二引 (7038), 始点 (6060), 機械 (6053), 番左 (5465), 270 (5266), column (4184), 引数 (3768), 第三 (3733), 第二 (3474), box (3171), 列= (2907), 2021 (2869), 解説 (2809), 対参 (2662), 相対 (2355), レイ (2211), index (2153), イア (1883), アウ (1856); 『左端のデータと同じ列・別の行の値を拾いたい』（梅）
横長の表で各項目行にフラグを立て、別の行(複数)に入力された数値を拾って集計のようなことをしています。フラグは表の範囲の一部で連続したセルに同じ数字が入っていて、そのフラグの入った左端の列の特定の行(1行)を集計値から省きたいです。左端のセルを取得する数式を調べて、INDEX関数の列の部分にあててみたりしたのですが上手く目的のセルを取得できませんでした…。どなたかご指南いただけると大変ありがたいです。よろしくお願いします。＜使用 Excel:Excel2016、使用 OS:Windows10 ＞ ---- 具体的なレイアウトをセル番地付で書いて頂くとイメージが涌くんですけどねぇ・・（半平太） 2021/02/02(火) 17:20 ---- レイアウト取得用のVBAです。 [[20110209184943]] ・都合悪い部分は伏字にしてOKです・フラグの有無を両方含んだ提示をお願いします。・レイアウト上もしくは文面にて、どこに何の集計をさせたいのか明記してもらえると分かりやすいです（理想とする結果もあるとベストです）（ななし） 2021/02/02(火) 17:40 ---- |[A] |[B] |[C]|[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O] |[P] [1]| |通し番号→| |基点 |・・・| 28| 29| 30| 31| 32| 33| 34| | | | [2]| |BOX番号→ | | 615|・・・| 695| 691| 692| 694| 719| 759| 763| | | | [3]| |BOX内長→ | | 6.000 |・・・| 1.270 |1.270 | 1.270 | 1.940 | 1.270 | 1.270 | 1.270 | | | | [4]| |BOX外長→ | |10.000 |・・・|12.730 |3.730 |13.730 |14.395 |36.930 |36.930 |11.230 |フラグ種別|外長合計 |BOX個数|内長合計 [5]|始点1:692| 692| | | | | | 2| | | | | 2| 13.73| 1| 1.27 [6]| | 679| | |・・・| 1| 1| 1| | | | | 1| 30.19| 3| 3.81 [7]| | 719| | | | | | 1| 1| 1| | | 1| 65.055| 3| 4.48 [8]| | 759| | | | | | 1| 1| 1| 1| | 1|=SUMIF($C8:$L8,"<=2",$C$4:$L$4)| 4| 5.75 お二方ありがとうございます。レイアウト、書こうとして挫折したのでVBAの提示も凄くうれしいです。わかりやすく多少編集しましたが、大丈夫ですかね…。表は始点1のBOXからそれぞれのBOX（B5〜B8）まで途中にあるBOXとBOX自体の長さ、BOX間の長さの表です。コードを引くのにその長さの合計を出したいです。 1〜4行目を別の一覧からコピペすれば汎用出来るものを目指しています。やりたい事↓ ・フラグ（1or2）の立ってる「BOX個数」「外長合計」「内長合計」を求めたい・「BOX外長」についてはフラグの一番左を減算したい（右→左にのびていて4行目は直前のBOXからの距離が入力されている為。 G4には28番695から29番691BOXまでの距離が入っている。）・フラグ2については始点と目的BOXがイコールなので外長は不要（データが一つでも一番左=その一つが減算出来れば問題なし。不可であればIF関数を使って考えようと思っています。）一番左のフラグを抜いて個数と内長に加算するのも考えたのですが、内長について左斜め上を得て加算するよりフラグの一番左と同列を減算の方が良い気がして INDEX+MATCH等自分なりに調べて試したのですが理解が足りず上手くいきませんでした。何か適切な方法があればご指南いただきたいです。よろしくお願いします。（梅） 2021/02/03(水) 10:51 ---- 提示されたN:P列(現実にはもっと右方にあるんでしょうが）の値は「正解値」なんですか? 説明と合っている様には思えないのですが。（半平太） 2021/02/03(水) 12:11 ---- ごめんなさい、表は右→左ではなく左→右の間違いです。（L列とM列の間がもっと長い） N列には現状N8で表示した数式が入っていてフラグが立っている全ての合計が出てしまっています。ここから一番左のフラグの外長を引きたいんです。 O列とP列は正解と思うのですが、どこかおかしいでしょうか? （梅） 2021/02/03(水) 13:15 ---- 途中の、だとおかしいですね。始点と目的BOXを含む全フラグ合計です。（梅） 2021/02/03(水) 13:21 ---- レイアウトありがとうございます。＞データが一つでも一番左=その一つが減算出来れば問題なし。ということは、692の外長は0になるで合っていますか? ちなみに、フラグ1の場合と2の場合でどこか処理は分かれますか? 全く同様の処理でいいのでしょうか。（ななし） 2021/02/03(水) 14:34 ---- ななしさんそれで合っています。フラグ1と2は試行錯誤の名残でして、＞データが一つでも一番左=その一つが減算これが可能であれば同様の処理で問題ありません。（梅） 2021/02/03(水) 14:50 ---- ごめんなさい、雑確認にしましたが… 恐らくやりたいことはできているかなと思います。 |[A]|[B]|[C]|[D]|[E] [1]| 10| 20| 30| 40| [2]| | 1| | 1|=SUMIF(A2:D2,"<=2",A$1:D$1)-INDEX(A$1:D$1,,AGGREGATE(15,6,COLUMN(A2:D2)/(A2:D2<>""),1)) [3]| 1| | 1| |30 [4]| | | 1| |0 これでOKそうなら範囲等修正して使用してみてください。（ななし） 2021/02/03(水) 16:22 ---- ななしさんありがとうございます! 本日確認が難しいので明日試してみますね。取り急ぎ御礼まで。（梅） 2021/02/03(水) 16:34 ---- ななしさん遅くなってごめんなさい。試してみたのですが、実際の表に当てはめると何故か左から3番目を減算してしまいます。表の一部を抜き出して数式の動作確認をすると正しい値が出るのですが…? |[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] [1]|電源 | 6.000 | 1.940 | 1.270 | 1.270 | 1.270 | 1.270 | [2]|0.000 |10.000 |14.030 |21.395 |48.730 |48.730 |45.530 | [3]| | | | | | | | [4]| | | | | | | | [5]| | | | | | | | [6]| 1| 1| 1| 1| 1| 1| |=SUMIF(A6:G6,"<=2",$A$2:$G$2)-INDEX($A$2:$G$2,,AGGREGATE(15,6,COLUMN(A6:G6)/(A6:G6<>""),1)) [7]| | | | | | | | 上記では正しく142.885が出ますが、実リストだと128.855が出ます。別の行でも同様に左から3番目が減算されてしまいます。相対参照にはフラグの立つ範囲を、絶対参照には相対参照と同数の列・集計したい行を入れています。何か原因思い当たりますでしょうか。（梅） 2021/02/05(金) 10:50 ---- 実リストのほうのレイアウトを見せていただいてもいいですか? 大きすぎても問題ないので、うまくいかないところが見たいです。＞相対参照にはフラグの立つ範囲を、絶対参照には相対参照と同数の列・集計したい行を入れています。この文と、上記の数式の直し方を見る感じ合っているとは思うのですが… 毎回3番目の値が抜かれるとすると、ズレているのはAggregateの数式内の参照範囲か第4引数ぐらいだと思います（ななし） 2021/02/05(金) 11:29 ---- 早速のご返信ありがとうございます。実リスト、列がDVまであるんですけどそのまま貼ってよいですか・・・? （梅） 2021/02/05(金) 11:48 ---- 思ったより大きいですね… 一旦うまくいかない式と、それが入力されているセルの場所、参照したい数値が何行目にあるかだけ教えてもらってもいいですか? （ななし） 2021/02/05(金) 11:59 ---- ですよね…私も貼ろうか迷ったんですけどさすがに憚られて。列DWに下記式が入っています。 =SUMIF(C8:DV8,"<=2",$C$4:$DV$4)-INDEX($C$4:$DV$4,,AGGREGATE(15,6,COLUMN(C8:DV8)/(C8:DV8<>""),1)) 集計したい数値は4行目、表の左上部だけレイアウト貼ります。 |[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] [1] | | | |基点 | 1| 2| 3| 4| 5 [2] | | |電源1 | | | | | | [3] | |内長 | | 6.000 | 1.940 | 1.270 | 1.270 | 1.270 | 1.270 [4] | |外長 |0.000 |10.000 |14.030 |21.395 |48.730 |48.730 |45.530 [5] |電源1|機械1| | | | | | | [6] | |機械2| | | | | | | [7] |電源2|機械3| | | | | | | [8] | |機械4| 1| 1| 1| 1| 1| 1| [9] | |機械5| | | | | | | [10]| |機械6| 1| 1| 1| 1| 1| 1| 1 [11]| |機械7| | | | | | | （梅） 2021/02/05(金) 12:07 ---- ああ、そのレイアウトだとそうなってしまいますね… 最初のレイアウトを失念していました。すみません。 COLUMN(C8:DV8) は、「シート全体のうち何行目か」を返す関数なので C列に対しては"3"という値を返します。しかし、index関数でほしい値は「指定した範囲の中で何列目か」なので、 2行ズレた値を取得してしまっています。そのため、この場合でしたら >=SUMIF(C3:DV3,"<=2",$C$2:$DV$2)-INDEX($C$2:$DV$2,,AGGREGATE(15,6,COLUMN(A3:DT3)/(C3:DV3<>""),1)) ^^^^^^^^^^^^^ このようにしてください。（ななし） 2021/02/05(金) 13:07 ---- できました!ばっちりです!! 私のレイアウトの省略の仕方が良くなかったです。ごめんなさい。お手数をおかけしました。度々ありがとうございます。後学のために INDEX($C$2:$DV$2,,AGGREGATE(15,6,COLUMN(A3:DT3)/(C3:DV3<>""),1) 図々しいお願いで恐縮ですが↑数式の解説をお願いしても宜しいでしょうか。 INDEX関数の概要は存じておりますが、AGGREGATE関数は初めて見ました。第一引数15=小さい方からの順位、第二引数6=エラー値を無視までは調べてすぐわかりましたが第三引数COLUMN(A3:DT3)/(C3:DV3<>"" ←ここがわかりません。また、INDEXの第二引数は行の指定のはずですがAGGREGATEで取得しているのは列ではないのか? トンチンカンな事言ってたらごめんなさい。ご面倒でしたら、スルーして頂いて大丈夫です。本当にありがとうございました。（梅） 2021/02/05(金) 13:45 ---- できたようで安心いたしました。私も学んだばかりですので怪しい部分もあるかもしれませんが、解説させていただきます。長文&分かり辛い部分も多々あるかと思いますがご了承ください。 index関数はご存知とのことなので、INDEX($C$2:$DV$2 は問題ないかと思われます。＞INDEXの第二引数は行の指定のはずですがAGGREGATEで取得しているのは列ではないのか? お察しの通り、取得しているのは列です。 INDEX($C$2:$DV$2『,,』AGGREGATE〜となっているため、第三引数に割当たっています。（行は今回1行のみのためスキップしていますが、あえて明示するのであれば INDEX($C$2:$DV$2,1,AGGREGATE〜と同じ意味です） AGGREGATEの説明ですが、前半の引数は調べていただいた通りです。まず前提として、Excel数式における配列を理解していることが前提になることをご了承ください。第三引数の COLUMN(A3:DT3)/(C3:DV3<>"") ですが、 COLUMN関数はさきほど書いた通り列番号を取得する関数です。それに対して、C3:DV3<>""の判定で割り算をしています。 Excelでの判定値は以下の通りで、Trueが1,Falseが0です。 http://officetanaka.net/excel/function/tips/tips59.htm そのため、C3:DV3のうち、空欄のセルはFalseの判定を返し、 COLUMN(A3:DT3)/(C3:DV3<>"") を行った際に分母が0 となります。分母が0ということは#DIV/0!のエラー値となります。つまり、第二引数でしている通り集計の時に無視される存在となるわけです。逆に、空白でないセルは1で割り算することになるため、その列の番号が返されます。そして第四引数は、集計された値のなかから(第一引数の指定順で並べた際に）何番目を選択するかを指定しています。今回は小さい順に並べた状態の1番目ということで、結果として一番左の列番号が返ることになります。（ななし） 2021/02/05(金) 14:18 ---- ご存知であれば蛇足ですが… 一応、配列の説明も追加でざっくり書きます。 COLUMN(A3:DT3)/(C3:DV3<>"") と書いたとき、 COLUMN(A3)/(C3<>"") , COLUMN(B3)/(D3<>"") , COLUMN(C3)/(E3<>"") ... という処理が一つの関数に詰まっている状態です。多分この辺は、今回回答した数式を数式の検証で見てみると感覚的に分かりやすいと思います（実データだと多くて分かり辛い場合、テストデータのほうがいいかも）（ななし） 2021/02/05(金) 14:23 ---- ななしさんありがとうございます!!感激です。『,,』がスキップだったんですね。確かに,,?はて?となりました。言われてみれば確かに。笑最後の引数を省略する形は何度も使っていますが、途中の引数も（場合によっては）省略可能なんですね。配列については、まだ理解が浅く… しかしながらCOLUMN(A3:DT3)/(C3:DV3<>"")の解説は大変わかりやすいです。 TRUE=1,FALSE=0という数値への割り当てはこのように使うんですね!! ところでCOLUMN(A3:DT3)/(C3:DV3<>"")の範囲がずれているのは先ほどの修正でもあった COLUMNとINDEXの参照基準の違いからくるものでしょうか? 素人目には列DUと列DVについてはどうなるのか心配になるのですが…。（梅） 2021/02/05(金) 14:51 ---- あまりこのような解説をしたことがなかったので不安でしたが、少しでも伝わったようで安心いたしました。＞ところでCOLUMN(A3:DT3)/(C3:DV3<>"")の範囲がずれているのは先ほどの修正でもあった COLUMNとINDEXの参照基準の違いからくるものでしょうか? そのとおりです。簡単にするために小さい範囲で説明しますと INDEX(C$2:F$2,,AGGREGATE(15,6,COLUMN(A3:D3)/(C3:F3<>""),1)) となっていた場合、「INDEX関数から見た」1の値はC列となります。同様に、4はF列です。ただし、「COLUMN関数から見た」1はA列なんです。4はD列です。 AGGREGATE関数は最終的にCOLUMN関数の値を返しますから、 COLUMN関数のD列 = 1 = INDEX関数のF列となるわけです。これをもし、COLUMN関数の範囲をCOLUMN(C3:F3)にしてしまうと COLUMN関数のF列 = 6 = INDEX関数の??列（範囲外のため認識できない) になってしまいます。（ななし） 2021/02/05(金) 15:10 ---- 度々重ねての質問でごめんなさい。 AGGREGATE関数は最終的にCOLUMN関数の値を返しますから、 COLUMN関数のD列 = 1 = INDEX関数のF列となるわけです。 ↑は COLUMN関数のD列 = 4 = INDEX関数のF列でしょうか…? 細かく解説していただき大変ありがたいです。完全に理解するにはまだまだですが、頂いた解説をもとに自分で勉強してみます。こちらで何度か質問をしていますが、皆さん本当に知識が広くかつ惜しむことなく教えていただき感謝してもしきれないです。本当にありがとうございます。（梅） 2021/02/05(金) 15:53 ---- > ↑は COLUMN関数のD列 = 4 = INDEX関数のF列でしょうか…? すみません、最初別の列にしていたので修正ミスですね。 4で合っています。 AGGREGATEは難しい関数だと思います（自分も、この掲示板での過去の解説をたくさん読んでようやく覚えたところです）ですが、使える状況も多く、便利な関数でもあります。過去ログに先人の方々の解説もありますので、そちらも是非参考にしてみてください。一例ですが… [[20191125111541]] （ななし） 2021/02/08(月) 12:23 ---- ななしさん参考までご提示いただき重ねて御礼申し上げます_(._.)_ まだまだ不勉強な部分が多く、ご面倒をおかけしました。丁寧に面倒見て頂いてビールでも甘いものでもお贈りしたい勢いです。お時間使って頂き本当にありがとうございました。精進します! （梅） 2021/02/08(月) 13:25 ...; https://www.excel.studio-kazu.jp/wiki/kazuwiki/202102/20210202160256.txt - [detail] - similar

PREV NEXT

訪問者：カウンタ