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

miyamo2 · July 26, 2020, 7:49am

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

<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 · July 26, 2020, 3:16pm

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

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

例えばこれを…

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

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

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

miyamo2 · July 27, 2020, 10:14am

文字化けになり、申し訳けありませんでした。「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 · August 1, 2020, 9:18pm

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 · July 26, 2020, 4:33pm

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

例示されてる関数（=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 · March 19, 2022, 10:41pm

サンプルの数式は 行列式 ( 配列数式 , 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（複雑そうに見えますが、考え方は大体一緒です）