У меня есть три колонки:
- Столбец A содержит число от 1 до 100, представляющее различные станции отбора проб. Номера идут по порядку.
- Столбец B независим от столбца A. Он содержит выбранные числа из столбца A (значения от 1 до 100), но не все, потому что я каждый раз получаю список с данными только с некоторых станций. Столбец B упорядочен в порядке возрастания, но короче столбца A, потому что в нем отсутствуют некоторые станции, и нет пустых ячеек, чтобы сохранить соответствие записей столбца B записям столбца A.
- Столбец C содержит данные, связанные со столбцом B, например, показания температуры на станции отбора проб или любую другую информацию.
Теперь я хотел бы избежать копирования всей информации вручную из столбца C туда, где она должна быть выровнена со столбцом A. Я хочу выровнять значения столбцов B и C со списком станций в столбце A, вставляя при необходимости пустые места в столбцы B и C. После этого столбец B можно будет удалить.
В качестве примера:
ColA ColB ColC
1 1 a
2 2 d
3 4 r
4 6 e
5 7 x
6 9 r
7 10 e
8 11 f
9 13 e
10 15 e
...,...,...
Должно после этого выглядеть так...
ColA ColB ColC
1 1 a
2 2 d
3
4 4 r
5
6 6 e
7 7 x
8
9 9 r
10 10 e
11 11 f
12
13 13 e
14
15 15 e
...,...,...
решение1
Версия TL;DR: поместите исходные данные в ячейку A3:B102, заполните ячейку D3:D102 числами от 1 до 100, вставьте =IFNA(VLOOKUP($D3,$A$3:$B$102,2, FALSE), "")
в ячейку E3, затем скопируйте ячейку E3 в ячейку E4:E102.
Исходя из вашего описания того, что вам нужно, я бы рекомендовал отделить сортировку от исходных данных и вместо этого иметь список станций отбора проб и использовать a VLOOKUP
для поиска данных для каждой из них.
Вам понадобятся три вещи.
- Место для вставки неотсортированного и/или неполного списка показаний данных.
- Здесь должно быть достаточно места для полного набора данных.
- В этом случае, поскольку у вас есть 100 станций отбора проб и по одному показанию на каждой станции, вам понадобится 100 строк и два столбца.
- Для моего примера это будет диапазонА3:Б102.
- Список станций отбора проб и место для размещения соответствующих показаний рядом с ними.
- В этот список должны быть включены все станции отбора проб в том порядке, в котором вы хотите их видеть.
- Опять же, мы смотрим на 100 станций отбора проб, пронумерованных от 1 до 100, и мы хотим, чтобы они были в числовом порядке. Это означает, что нам нужно заполнить один столбец числами 1, 2, 3 и т. д. до 100, а столбец рядом с ним будет содержать формулу.
- В моем примере список станций отбора проб будетД3:Д102, и формула будет скопирована во все ячейкиЭ3:Э102.
- Формула, которая помещается в столбец «Чтение» отсортированных данных и ищет соответствующие данные в несортированных данных.
- Вам следует понимать как абсолютные, так и относительные ссылки, поскольку эта формула использует обе:
- Большинству людей знакомы относительные ссылки, такие какД3. Эти ссылки изменяются при копировании из одной ячейки в другую.
(Например, если вы=D3
вставляетеЕ3, затем скопируйтеЕ3кЕ4, новая копия вЕ4буду читать=D4
.) - Абсолютные ссылки содержат
$
в столбце и/или строке символ , чтобы предотвратить их изменение при копировании.
(Например, столбец никогда не изменится при копировании=$D3
, а строка изменится; аналогично, с=D$3
, столбец изменится, а строка — нет; и, наконец,=$D$3
всегда будет ссылаться на эту ячейку, никогда не изменяясь при копировании.)
- Большинству людей знакомы относительные ссылки, такие какД3. Эти ссылки изменяются при копировании из одной ячейки в другую.
- Формула помещается в верхнюю строку отсортированных показаний данных, а затем ее следует скопировать в следующие 99 ячеек под ней.
- Формула для поиска правильных данных, которые входят вЕ3есть
=VLOOKUP($D3,$A$3:$B$102,2, FALSE)
, но это помещает#N/A
в ячейки, которые ссылаются на несуществующие данные. Если вы хотите пробелы вместо ,#N/A
вы захотите поместитьVLOOKUP
внутриIFNA
.
- Вам следует понимать как абсолютные, так и относительные ссылки, поскольку эта формула использует обе:
Полная формула для этого примера выглядит следующим =IFNA(VLOOKUP($D3,$A$3:$B$102,2, FALSE), "")
образом, а вот как выглядят результаты:
решение2
- Определите диапазон данных, который вы хотите отсортировать (Меню/Данные/Определить диапазоны)
- Настройте параметры сортировки в Меню/Данные/Сортировка,
- выберите столбцы в нужном вам порядке (вкладка «Критерии сортировки»)
- отметьте правильно, если диапазон содержит метки столбцов.
- отметьте «Копировать сортировку в» и введите диапазон назначения (все столбцы будут скопированы)