☰

セル内の文字列を、何らかの方法で行と列の範囲の配列に変換できますか?

tag-icon • tag-icon microsoft-excel
セル内の文字列を、何らかの方法で行と列の範囲の配列に変換できますか?

これらの名前をリストから、画像に示すような範囲マトリックスに変換するつもりです。数式をデバッグするときに、行と列の範囲の配列を正確に表示したいのですが、これらの名前をこのような範囲に変換する方法はありますか?ここに画像の説明を入力してください

答え1

テキスト文字列が必要な場合は、次を使用します。

="={"""&TEXTJOIN(""",""",,SUBSTITUTE($A$2:$A$5," ","""/""")) & """}"

他の場所で使用される実際の配列が必要な場合は、次を使用します。

IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(TRANSPOSE($A$2:$A$5)," ","</b><b>")&"</b></a>","//b["&ROW($A$1:$A$4)&"]"),"")

これにより、他の数式で使用できる 4x4 配列が作成されます。バージョンによっては、これを別の数式で使用するには、編集モードを終了するときに Enter ではなく Ctrl + Shift + Enter を使用して配列数式を強制する必要があります。

F9数式バーに配列を表示して、必要な操作を実行する方法はわかりません。

答え2

さて、私が再現するつもりのないタイプミス「JONH」を除いたリテラル文字列は、Scott Craner の式をわずかに調整することで取得できます (そして、私はトリックSEQUENCE()の代わりにROWS()新しい関数を使用して慣れる必要があります)。次に、それを文字列処理の基礎として使用します。

="={"""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(B234:B237),SEQUENCE(4)))&""","""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(C234:C237),SEQUENCE(4)))&""","""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(D234:D237),SEQUENCE(4)))&""","""&TEXTJOIN("""\""",,INDEX(TRANSPOSE(E234:E237),SEQUENCE(4)))&"""}"

文字通り望ましい出力が得られますが、出力はないExcel の観点からは配列定数であり、F9結果として正確には使用されません。

Excel にとって、これは単なるテキスト文字列です。したがって、数式内で使用すると、失敗するか、まったくの偶然で成功するかのどちらかになります。場合によっては、正しく機能することもあります。ただし、Excel の内部使用配列定数で何かをハイライトしてクリックするとF9作成されるものとはまったく同じまたは同等ではありません。

とはいえ、これは醜くて肥大化しています。私はコメントでスレッドを前後に追っているわけではないので、Scott Craner がそれを使用したためTRANSPOSE()に私もそうせざるを得なかった理由がわかりません。必要な出力は左から右のようです (上記のコメントで "A234 = "Jonh"、...)。そのため、これを削除し、A2:A5 を A1:A4 に調整します (または、私の実験では行を削除します)。次のようになります。

=IFERROR(FILTERXML("<a><b>"&SUBSTITUTE(($A$234:$A$237)," ","</b><b>")&"</b></a>","//b["&SEQUENCE(1,4)&"]"),"")

彼の式を参考にして自分の式を作り直し、それをブレンドして単一の式にすると、次のようになります。

="={"""&TEXTJOIN("""\""",、B234:E234)&""","""&TEXTJOIN("""\""",、B235:E235)&""","""&TEXTJOIN("""\""",、B236:E236)&""","""&TEXTJOIN("""\""",、B237:E237)&"""}"

ちなみに、これはパターン化され、構造が反復的であるため、古いCONCATENATE()またはunion operator今日のトリックを使用して、行と列をうまく調整して最終的に連結し、値として貼り付け、その前に「=」を配置して数式にすることで、大幅に構築できます。これでさらに簡単になりました。4 つの小さな行と 5 つ未満の要素を含まないデータの場合。

そうすると、表現された目的の結果文字列が得られますが、繰り返しになりますが、これは、F9前述のように使用後に表示される配列定数と同じものとして Excel が認識する結果ではありません。

そして、変更されたスコット・クレイナー式は、コメントで説明されている個々のセルの結果を返します。これは、Excelにその内部配列定数を作成させるための式内で使用できます。ただし、文字列式（計算上は行き止まりです）を使用するのではなく、so Excel will evaluate it to the desiredスコット・クレイナーの（調整された）式を配列定数で必要な式の中で使用しますそして、これらの 16 個のセルを埋めるのではなく、その数式内の計算チェーンに渡します。

サウスパークでカートマンが復讐する相手が「スコット・テナーマン」（またはそれに近い名前）だと気づきました。もう「スコット・クレイナー」と何度も言っても少しも気になりません！

関連情報