エクセルで，リストから同じ列を判別してその列または何か値を抽出する方法

Question

Excel365にて，リストから同じ列を判別してその列または何か値を抽出する方法を探しています。

具体的には以下のように元の数字の羅列から全通りの組み合わせを作成します。

元データ 組み合わせを全通り作成 返り値用
0 0 0 0 0 0 　　　　　　　 0
1 1 1 0 0 1 　　　　　　　 1
2 2 2 0 0 2 　　　　　　　2
3 3 3 0 0 3 　　　　　　　3
4 4 4 0 0 4 　　　　　　　4
5 5 5 0 0 5 　　　　　　　5
6 6 6 0 0 6 　　　　　　　6
7 7 7 0 0 7 　　　　　　　7
8 8 8 0 0 8 　　　　　　　8
9 9 9 0 0 9 　　　　　　　9
（以下省略）

その後，別シートの　2 1 0　の組み合わせが，作成した組み合わせのどの位置にあるかをシート100枚ほど判別していきたいと考えています。

これをもとにクラスタリングを行っていくため，単純に上からの行番号が返ってきても，返り値用の値でも構いません。

シート数が多いのと，この作業がいくつかあるのでできれば自動化したいと考えていますが，手作業でもなにか良い方法はありませんでしょうか。

ご教示いただけますと幸いです。

（画像1）

（画像2）

Comments

[can110]

単純に組み合わせの数の羅列を足したものを識別子（クラスタリングの値）とするんじゃダメでしょうか？
たとえば別シートの「2」と「1」と「0」の組み合わせは「210」という識別子に対応します。

[退会済みユーザー]

ありがとうございます。
それがこの組み合わせがシートによって変動し，4行～8行ほどありますので，組み合わせを新しく識別子にしたいのです，，，

[can110]

どんな組み合わせ「a, b, c」であれ、識別子「abc」にできます。
目的を達するにあたって、何が、どの部分の処理が具体的に分からないでしょうか？
・複数のシートを列挙する方法、あるいは順番に処理する方法
・シート内で組み合わせが格納されたセル範囲を知る方法
・などなど

[退会済みユーザー]

すみません、先ほど意図を理解しておりませんでした。数字の羅列にすると言うことですね。なるほど。

わからないのはリストからシート内で組み合わせが格納された範囲を知る方法です。前者の処理も含めたマクロが書ければよいですが、複雑ですのでまずは後者を進めたい所存です。

[aokikenichi]

PythonタグがありますがPythonコードでもよろしいでしょうか

[radames1000]

サンプル画像はれませんか？元シートと別シートの。
別シートにどんな形で値が入力されているのか（列固定かフリーか等）知りたいです。
値は複数のセルにわかれているんですよね？（組み合わせは3~8桁の場合もあり？）

[退会済みユーザー]

aokikenichi様
pythonコードでも大丈夫です。助かります。お願い致します。

[退会済みユーザー]

radames様
画像2のB8C8D8のセルの値の組み合わせが，画像1のEFG列のどの位置にあるかを計算させたいです。
値は分かれています。組み合わせは（画像2）のB2の位置にあるAやBなどのカテゴリによっては全部で52万通りほどあるものもあります。

[can110]

「値は分かれています。」以降の説明がよく分かりません。
E列以降もこの問題に関係してくるのでしょうか？
(画像２)における対象の領域（組み合わせ）は「B8:D11」の「2 1 0」「0 0 0」「0 0 0」「0 0 0」
の４つでなないのでしょうか？

[退会済みユーザー]

組み合わせは3～8桁の場合とは組み合わせの数だと思い記述いたしました。
現状質問している問題ではおっしゃる通り「B8:D11」の「2 1 0」「0 0 0」「0 0 0」「0 0 0」
の４つです。

E列以降もまた別シートで参照していきますが，ひとまずやり方がわかれば適用していくだけなので。
わかりにくくさせてしまい，申し訳ないです。

[sinzou]

画像1のABC列の値は、最大値が変動し、その桁の進数となるなら

画像２のB8C8D8の返り値は、

例）画像1("Sheet1")元データ各列の最大９８までとする
=D8+C8*(MAX(Sheet1!C2:C100)+1)+B8*(MAX(Sheet1!C2:C100)+1)*(MAX(Sheet1!B2:B100)+1)

となりませんか？

[退会済みユーザー]

sinzou様
これでもいけました。ありがとうございます。

Answer 1

Sheet2のB8C8D8からとる場合の数式サンプルです。

=SUMPRODUCT((E:E=Sheet2!B8)*(F:F=Sheet2!C8)*(G:G=Sheet2!D8)*I:I)

ただし、すべて「数値」として考えております。

Comments

[退会済みユーザー]

これでいけました。ありがとうございます。

Answer 2

わからないのはリストからシート内で組み合わせが格納された範囲を知る方法です。

まず、別シートの組み合わせのある領域は、全シート共通で

• １行目から始まっている。あるいは１行目は見出し行で２行目から始まっている
• 列の位置は共通で固定であり、たとえばA～Cのように連続している

ものとします。すなわち組み合わせの入っている最終行位置だけ分かればよいものとします。
ExcelであればRange("A:C").End(xlDown).Rowで取得できます。
Pythonであればopenpyxlで取得できるエクセル表のセル範囲についてに記載あるように、シートの属性として取得できます。