☰

カンマのない単一のセル内の数字を分割して複数の行に配置するにはどうすればよいでしょうか?

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-access
カンマのない単一のセル内の数字を分割して複数の行に配置するにはどうすればよいでしょうか?

私がやりたいことは次のとおりです。スプレッドシートに、数字の文字列 (一部は他のものより長い) が含まれるセルがあり、8 つの数字ごとに抽出して 1 行にまとめる必要があります。

現在のスプレッドシートは次のようになります。

IFIELD1     IFIELD2   IFIELD3     IFIELD4    IMAGELIST
05/30/2009  ERMS      100 Day     L. C.      0000001200000013
05/30/2009  ESE       20 Day      J. R.      0000001400000015
05/30/2009  IHS       100 Day     H. W.      00000016
05/30/2009  LMS       100 Day     D. E.      0000001700000018

私が求めているのはこれです:

IFIELD1     IFIELD2   IFIELD3   IFIELD4   IMAGELIST
05/30/2009  ERMS    100 Day     L. C.     00000012
05/30/2009  ERMS    100 Day     L. C.     00000013
05/30/2009  ESE     20 Day      J. R.     00000014
05/30/2009  ESE     20 Day      J. R.     00000015
05/30/2009  IHS     100 Day     H. W.     00000016
05/30/2009  LMS     100 Day     D. E.     00000017
05/30/2009  LMS     100 Day     D. E.     00000018

数字のセットによっては、8 のグループが 30 個以上並んでいるものもあります。1000 行を超えるデータを分離する必要があります。このプロセスを楽にするためのコードはありますか? 現在、左、中央、右を使用していますが、完成するまでに何年もかかります。ワークシートは Access から作成しているので、Access でもっと簡単に完成できる方法があれば、ぜひ提案してください。最終製品は csv 形式で保存する必要があります。また、画像が配置されている場所へのリンクを作成できるように、完成した製品の 8 桁全体を csv 形式で保存する必要があります。ご協力ありがとうございます。

答え1

Access には非常に簡単な解決策があります。

  1. 数字のリストのみのテーブルを作成します。1, 2, 3, 4, ... nは、nに含まれる項目の最大数ですIMAGELIST。 ただし、必要以上に大きくしてもコストはそれほど高くないので、50 程度まで上げてください。
  2. 元のテーブルと数値テーブルを取得するクエリを作成します結合なしこの時点で実行すると、元のテーブルのすべてのレコードのコピーSELECT *が取得されます。n
  3. フィールドを取得する代わりに、数値テーブルの値に基づいてフィールドで関数IMAGELISTを実行する数式を追加します。MIDIMAGELIST
  4. 数式が空の文字列を返さないレコードのみを表示するようにフィルターします。これにより、24 文字の長さのレコードが、多数の空のレコードが表示IMAGELISTされるのではなく、3 回だけ表示されます。n

このクエリの SQL は次のとおりです。

SELECT Table1.ID, Table1.IFIELD1, Table1.IFIELD2, Table1.IFIELD3, Table1.IFIELD4, Table2.Number, Mid([IMAGELIST],([Number]-1)*8+1,8) AS ImageListParse
FROM Table1, Table2
WHERE Mid([IMAGELIST],([Number]-1)*8+1,8)<>""
ORDER BY Table1.ID, Table2.Number;

私の簡単なモックアップでは、Table1は元のテーブル、Table2は数値テーブル、Numberは唯一のフィールドで、そこには 1 から n までの数字だけが含まれています。そして、並べ替えのためだけにTable2自動番号フィールドを追加しました。IDTable1

ここでは、 内のすべての項目がIMAGELIST正確に 8 文字であることを前提としています。長さが異なる場合、この方法は機能しません。

答え2

Sheet1Excel ワークシート関数を使用すると、これを実行するのはそれほど難しくありません。行 1 にヘッダーが付いたデータがあると仮定します。 を作成しますSheet2。次のように入力します。

  • A1→=Sheet1!A1
    • 右にドラッグしてすべての列をカバーします（つまり、  E5つの列の例に基づいて、列まで）。
    • Sheet1または、 からへのヘッダーをコピーするだけですSheet2。
  • A2→=OFFSET(Sheet1!A$1, $F2-1, 0)
    • 右にドラッグしてすべての列をカバーしますを除いて IMAGELIST (つまり、列 を通じて D)。
    • Sheet2ある時点で、列（日付を含む列など）を正しくフォーマットする必要があります。
  • E2→=LEFT($G2, 8)
  • F2→2
  • G2→=OFFSET(Sheet1!E$1, $F2-1, 0)
    • (これは、列 、列 を参照することを除いて、列A-の数式と同じです 。)DSheet1E
  • H2→=RIGHT($G2, LEN($G2)-8)

2行目を3行目にドラッグ/入力し、

  • F3に=IF(H2<>"", F2, F2+1)、そして
  • G3に=IF(H2<>"", H2, OFFSET(Sheet1!E$1, $F3-1, 0))

次に、すべてのデータを取得するために必要な行数分、行 3 を下にドラッグ/入力しますSheet1。

次に、値と形式をコピーして貼り付けます。

説明：

  • 列は Fどの行にあるかを示しますSheet1 これ行 ( Sheet2) からデータが取得されます。
  • 新しい行に進むたびにSheet1（つまり、列が増加するたびに F）、列は G完全な8倍になります。んIMAGELISTから桁数の値を取得しますSheet1。
  • Column の値は G分割され、最初の 8 桁が Column に E、残りが Column に 分割されますH。
  • Column に空でない余りがある限り H、それを Column にコピーし G、 Column を Fそのままにします (つまり、 の同じ行にアクセスしますSheet1)。
  • 列のデータがなくなると H、8×んから桁数のIMAGELIST値Sheet1ん行が にあるSheet2ので、 Column を増分し Fて次のIMAGELIST値を取得します。

答え3

目標を達成するには、次の 2 つのステップを実行する必要があります。

1 数字を異なる列に分割します。例: Excel 2010 の場合: データ - テキストを列に分割 - 固定幅 - 幅を 8 に設定して、[完了] をクリックします。

2 2 番目のステップは、データをアンピボットすることです。残念ながらこれは簡単ではありませんが、インターネットには次のような多くの解決策があります。Excel で「ピボット解除」または「ピボットの逆変換」を行うことは可能ですか?

関連情報