☰

Как разбить число в одной ячейке, в которой нет запятых, и поместить его в несколько строк?

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-access
Как разбить число в одной ячейке, в которой нет запятых, и поместить его в несколько строк?

Итак, вот что я собираюсь сделать. У меня есть электронная таблица с ячейкой, в которой находится строка чисел (некоторые длиннее других). Мне нужно извлечь каждые восемь чисел и поместить их в отдельные строки.

Вот как выглядит текущая электронная таблица:

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

Некоторые наборы чисел доходят до 30 или более групп по 8 в строке. С более чем 1000 строк данных для разделения. Есть ли какой-либо тип кода, который поможет сделать этот процесс менее пугающим? В настоящее время я использую Left, mid и right, но мне потребуются годы, чтобы закончить это. Рабочие листы выходят из доступа, поэтому, если есть более простой способ заполнить их там, я открыт для предложений. Конечный продукт должен быть сохранен в CSV. Также мне нужно сохранить все 8 цифр в завершенном продукте перед сохранением в CSV, чтобы я мог создать ссылку на то, где находится это изображение. Спасибо за вашу помощь!

решение1

В Access есть довольно простое решение.

  1. Создайте таблицу, которая представляет собой всего лишь список чисел: 1, 2, 3, 4, ... nгде nнаходится наибольшее количество элементов, которые когда-либо будут в вашем IMAGELIST. Однако сделать ее больше, чем необходимо, не так уж и дорого, так что доведите ее до 50 или около того.
  2. Создайте запрос, который извлекает исходную таблицу и таблицу чисел.без каких-либо соединений. Если бы вы это сделали SELECT *сейчас, вы бы получили nкопии каждой записи в исходной таблице.
  3. Вместо того чтобы вставлять IMAGELISTполе, добавьте формулу, которая выполняет MIDфункцию в IMAGELISTполе на основе значения из вашей таблицы чисел.
  4. Фильтр для отображения только тех записей, где формула не возвращает пустую строку. Это будет означать, что запись длиной в IMAGELIST24 символа будет отображаться только 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это единственное поле Table2, и это только числа от 1 до n, а поле автонумерации я добавил IDтолько Table1для сортировки.

Все это предполагает, что каждый элемент состоит IMAGELISTровно из 8 символов. Если длина различается, этот подход не сработает.

решение2

Это не так уж и сложно сделать с помощью функций листа Excel. Предположим, что ваши данные находятся Sheet1в заголовках в строке 1. Создайте Sheet2. Заполните ее следующим образом:

  • A1→=Sheet1!A1
    • и перетащите вправо, чтобы охватить все столбцы (т.е. через Column  E, основываясь на вашем примере с пятью столбцами),
    • или просто скопируйте заголовки из 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- D, за исключением ссылки Sheet1на столбец  E.)
  • 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 этотстрока (on Sheet2) извлекает данные из.
  • Каждый раз, когда мы переходим к новой Sheet1строке (т.е. каждый раз, когда Column  Fувеличивается), Column  Gполучает полную, 8×н-значение длиной в цифру IMAGELISTиз Sheet1.
  • Значение в столбце  Gразделяется: первые восемь цифр в столбец,  Eа остаток в столбец  H.
  • Пока у нас есть непустой остаток в Column  H, мы копируем его в Column  Gи оставляем Column  Fбез изменений (т.е. обращаемся к той же строке в Sheet1).
  • Когда у нас заканчиваются данные в столбце  H, это означает, что мы разложили 8×н-значение длинной цифры IMAGELISTиз Sheet1внстрок на Sheet2, поэтому мы увеличиваем Column  Fи извлекаем следующее IMAGELISTзначение.

решение3

Для достижения цели вам необходимо сделать два шага:

1. Разбейте числа на разные столбцы, например, в Excel 2010: Данные - текст по столбцам - фиксированная ширина - установите ширину 8, затем нажмите «Готово».

2 Второй шаг — отменить поворот ваших данных. К сожалению, это не так просто, но в Интернете есть много решений, например, это:Можно ли «отменить поворот» или «обратить поворот» в Excel?

Связанный контент