excelで動く数式がcalcでERROR 代替の方法はないでしょうか

[miyamo2]

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法、
たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
7/1 りんご 100円
7/3 みかん 100円
このように別のセルに抜き出す方法の質問があって、その回答が、
ーーーーーーーーーーー
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は５００行までとしていますが、必要に応じて変更して下さい

[JO3EMC]

えっと…。文字化けもあって、何がなんだかちょっとわかりにくいです。
元の表がイメージできません。
記載いただいているのは、何かの形式なのでしょうか…？（浅学ですみません…。）

ファイルなり画像の添付は出来ないでしょうか。

例えばこれを…

7/1	りんご	100円
7/2	ぶどう	200円
7/2	すいか	300円
7/3	みかん	100円

こうしたい、ってことですかね？

7/1	りんご	100円
7/3	みかん	100円

[miyamo2]

文字化けになり、申し訳けありませんでした。「Comments」をいただいた通りです。sheet1に上の表があり、sheet2のA1に条件100円を入れ、その下に条件に合う行を表示するため If 以下の数式が入っています。（Excelでは機能します。）

sheet1! を sheet1. に変えて「#NAME?」のエラーは消えましたが、２行目は０、３行目にぶどうが出ました。

Indexの中に、＊が入っているのは、私もよく理解できていないのですが、ExcelではIndexの引数として配列が使用できるようで、｛ ｝でくくった表示になるようです。それを＊を使っても実現できるようなのです。
一方、http://www009.upp.so-net.ne.jp/OpenOffice/excel-calc/function01_e-c004.html のIndexの説明によると、

INDEX関数は、行番号と列番号で指定したセルの内容を返します。
ExcelのINDEX関数には、「セル範囲形式」と「配列形式」の2つの書式があります。Calc のINDEX関数は、「セル範囲形式」の書式に限られます。

とあります。したがって、calc では仕様的に実現できない要求なのかもしれません。

ご提示いただいたプログラムありがとうございます。Excelでは数式１つでできたこともcalcでは順序を経てできるということですね。内容をよく勉強させていただきます。ありがとうございます。

[himajin100000]

https://opengrok.libreoffice.org/xref/core/include/formula/paramclass.hxx?r=d0ded163&fi=ReferenceOrForceArray#16

https://opengrok.libreoffice.org/xref/core/sc/source/core/tool/parclass.cxx?r=71665582#163

[JO3EMC]

スプレッドシートのデータ互換はあんまり得手じゃないのですが…、ちょっと試してみたところで少しご提案を…。




例示されてる関数（=IF～ってやつ）の「Sheet1!」を全て「Sheet1.」に置き換えてみるとどうでしょうか。




=IF(COUNTIF(Sheet1.$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1.A:A,LARGE(INDEX((Sheet1.$C$1:$C$500=$A$1)*ROW(Sheet1.$C$1:$C$500),),COUNTIF(Sheet1.$C:$C,$A$1)-ROW(A1)+1)),"")




Caclではシート指定の表現がExcelと違うようです（その程度のユーザーですすみませんw）。手元ではそうすることでエラーは出ません。
ただ…抽出されたのは「100円」じゃない「ぶどう」と「すいか」でした。




関数式の内容までは今のところ検証してません。手元にはExcelがなく、例示の関数式がExcelで機能してくれるのかどうかも検証できてません。自分には理解の及ばない部分もありそうです（INDEX式の使い方が理解できてません）。


正直のところ、表計算ソフトに向いた処理ではないように思えます。データベースやスクリプト向きと。




お手元にExcelがあるのなら、Excelでファイルを作ってCalcで読み込んでみるのが楽な気はします（邪道？）。




（追記）


質問の関数式とはアプローチが違いますが…。自分ならこう書く、という例を上げておきます。


257101A1.ods


中間計算セルを多用します。1セルにあまり複雑な式を盛ることはしません。もう少し統合して中間計算セルを減らすことが出来ますが、「検索範囲先頭行番号」と「検索範囲内出現位置」の列は無くせません。邪魔なら非表示なりシートを分けるなり、やりようはあろうかと思います。


範囲参照もなるべく扱いません。デバッグが面倒なので。なるべくシンプルに。あまり高級な機能は使わずに。


もっとスマートな書き方もあろうかと思いますが、参考として。

[nobu]

サンプルの数式は 行列式 ( 配列数式 , CSE数式 ) として入力する必要があります

SheetName : Sheet1

＼	A	B	C
1	日付	商品	価格
2	7月1日	りんご	100円
3	7月2日	かき	400円
4	7月3日	ぶどう	200円
5	7月4日	すいか	300円
6	7月5日	みかん	100円
7	7月11日	ぶどう	100円
8	7月13日	なし	100円
9	7月15日	みかん	200円
10	7月16日	すいか	300円
11	7月21日	みかん	100円
12	7月22日	ぶどう	200円

=IF(COUNTIF(Sheet1.$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1.A:A,LARGE(INDEX((Sheet1.$C$1:$C$500=$A$1)*ROW(Sheet1.$C$1:$C$500),),COUNTIF(Sheet1.$C:$C,$A$1)-ROW(A1)+1)),"")

まず行列式 ( 結果が１行 x１列 ) として入力した後
右または下にコピーすると A1、A:A が順次更新され。そのセルに合った値になります

---

行列式

https://help.libreoffice.org/7.3/ja/text/scalc/01/04060107.html

• 関数ウィザード の左下 [ 行列 ] して OK
• 数式編集後 Ctrl + Shift + Enter
  等

---

参考数式

Calcでは同内容の数式は以下のような 行列式 で書くことができます

---

解りやすくするため、データ範囲に 名前付き範囲 を設定

DATA  = $Sheet1.$A:$C   (データ範囲)
PRICE = $Sheet1.$C$1:$C$500  (価格範囲)

最大 10件 表示（出力範囲を自動的に伸縮する機能は無いので最大値を決めておく）

=IFERROR(INDEX(DATA,SMALL(IF(PRICE=A1,ROW(PRICE)),ROW(1:10))),"")

この数式は Index関数の仕様が少し異なるため Excel との互換性はありません

Ask_55063_選択価格で列挙.ods (11.7 KB)

---

参考Ask（複雑そうに見えますが、考え方は大体一緒です）

Calc: Exclude rows from INDEX() reference

A sample that I think does what you like. [image description] Maybe with a pivot table it’s easier and can be filtered as you like. Pivot table sample

Array formula won't update correctly (not even with F9)

[image description] columns: A B C D E row1 Item Value Lookup Count Value row2 C 23 C 3 23 row3 X 34 - - 56 row4 C 56 - - 67 row5 C 67 row6 X 78 Defined Name ranges: A2:A6 = Item B:B = Value Formulas: D2: =COUNTIF(item,C2) Array Formulae E2: {=IF(ROWS(E$2:E2)>…

How to Lookup First Non-zero value in a column and copy it to a cell

{=IFERROR(INDEX($LEDGER.G1:G23;SMALL(IF(N($LEDGER.G1:G23)<>0;ROW($LEDGER.G1:G23);"");1));"")} and {=IFERROR(INDEX($LEDGER.G1:G23;SMALL(IF(N($LEDGER.G1:G23)<>0;ROW($LEDGER.G1:G23);"");2));"")} Do not forget to finish entering the formula with the key combination Ctrl+Shift+Enter!

New update 7.2.1.2 ruined my array calculations

That is a bug that will be fixed in 7.2.2. The problem in the attached document is that the COUNTIF($V10:V$10,V10) to COUNTIF($V$10:V18,V18) expressions yield a 0 result, for which passed as Rank to SMALL() it then rightly complains with an error. You can inspect that by invoking the Function Wizard (Ctrl+F2) on such formula and looking at its Structure page and by stepping through with the Next button. Btw, the =IF(ISERROR(INDEX($B$10:$B$18;SMALL(IF($K$10:$K$18=V10;ROW($K$10:$K$18)-ROW($K$9))…

Can't put ifandif in formula

Hope I can explain this without being too confusing! I have a formula that works in ms excel but gets a #value! error, when there are 2 IF checks together. What I’m trying to do is pick the top ranking scores in male female categories. Here is a copy of my formula that works, except it does not select whether or not the person is male or female. =IF(A37="","",INDEX($‘par équipe’.$B$2:$B$101,SMALL(IF($‘par équipe’.$C$2:$C$101=B37,ROW($‘par équipe’.$C$2:$C$101)-ROW($‘par équipe’.$C$2)+1),COUNT…