Расскажу про разные функции и формулы в Экселе, которыми пользуюсь при формировании таблиц выбора. Не всегда данные поступают в удобном виде, иногда их приходится форматировать или создавать заново. И тут пригодятся широкие возможности Экселя. Пойдём от совсем простого к более сложному.

1. Объединение значений

В отличие от Ревита у Экселя нет проблем с суммированием разных значений. Это здорово выручает, когда нужно сформировать наименование или маркировку элемента: объединяем значения из разных столбцов и готово.

Сделать это можно двумя путями: либо специальной функцией СЦЕПИТЬ, либо соединением ячеек и текста через символ &.

Данные в Экселе. В первом столбце пользуюсь соединением ячеек через &, в последнем — через функцию «СЦЕПИТЬ»
Длинное наименование сформировал через функцию СЦЕПИТЬ
Короткое сделал через объединение символом &

Если нужно добавить текст или разделитель, то пишите его в кавычках.

2. Распространение формулы на весь столбец

Скорее всего, все это и так знают, один я, как лошара, узнал только в этом году. Раньше писал формулу в ячейке, а потом тащил за правый нижний край ячейки до самого конца таблицы. Руками. Даже если таблица очень большая.

Потом узнал, что всё куда проще: достаточно дважды кликнуть на нижний правый угол таблицы и вуаля — формула распространится на весь столбец.

3. Заполнить пустые ячейки повторяющимися значениями

Открываете какой-нибудь ГОСТ или сайт, там таблица с диаметрами, но значения объединены, чтобы глазами было проще читать и меньше набирать текста. Глазам это, может, и удобно, а вот машиночитаемость такой таблицы равна нулю. Приходится копировать её и вставлять в Эксель для обработки.

После вставки получается что-нибудь вот такое:

Важный момент — в первой строке все значения должны быть заполнены.

Допустим, что таблицу почистили, всё настроили и проверили, что на самом верху всегда то значение, которое должно повторяться до тех пор, пока не появится новое. Например, в третьем столбце значение 273 будет повторяться в столбце до тех пор, пока не начинается новое значение 325.

Алгоритм такой: выделяем таблицу → нажимаем F5 → в окошке жмём «Выделить» → пустые ячейки → ОК → нажимаем F2 (команда ставит курсор в ячейку) и пишем знак равно = → выделяем ячейку выше, в которой есть значение → Ctrl + Enter.

Выделяем таблицу и жмём F5
Ставим точку на пустые ячейки
Выделяются пустые ячейки. Жму F2, появляется курсор
Пишу знак равно и указываю ячейку над той, в которой формула
Жму Ctrl + Enter — получаю результат

Метод классный, но нужно тщательно готовить данные. Бывает, что после копирования и вставки таких таблиц значение из объединённой ячейки при отмене объединения оказывается не наверху, а в середине таблицы. В таком случае значения раскопируются неправильно. Поэтому будьте внимательны.

Даже с учётом такой подготовки алгоритм экономит время. Без него я бы не сделал свою библиотеку фланцев:

Библиотека: фланцы стальные по ГОСТ 33259-2015, 12820-80 и 12821-80

4. Вернуть значение из таблицы по ключу

Заголовок непонятный, поэтому опишу на примере. Есть список условных диаметров труб и их наружных диаметров. Составляю таблицу выбора для тройников, в неё нужно добавить наружные диаметры, чтобы при построении геометрии внешние диаметры тройника были такими же, как у трубы. Ну вот надо мне так и всё.

Тут нам поможет чудесная функция ВПР — вертикальный просмотр. Это что-то типа формулы для таблицы выбора в Ревите, о которых можете почитать большую статью, но попроще, так как работает только с одним аргументом.

Смотрим на примере. Вот заготовка таблицы, вписывать руками не хочется, тут у меня 24 с лишним тысячи строк:

В параметры D1 и D2 надо вписать наружные диаметры соответственно для магистрали DN1 и для ответвления DN2. Пишу формулу с ВПР. Синтаксис такой:

= ВПР(Искомое значение; Таблица для поиска; Номер столбца, откуда берём значение; Интервальный просмотр)
  • Искомое значение — это номинальный диаметр стороны тройника.
  • Таблица для поиска — два столбца с номинальными и внешними диаметрами трубы.
  • Номер столбца — номер столбца в таблице для поиска, откуда возвращаем значение, в нашем случае в таблице два столбца, внешний диаметр во втором столбце.
  • Интервальный просмотр — без понятия, что это, всегда ставлю ЛОЖЬ, чтобы искал точное совпадение.
Формула с ВПР
Результат работы формулы

Эксель берёт диаметр тройника, ищет его в таблице номинальных и внешних диаметров, находит в таблице такой же номинальный диаметр, как у тройника и возвращает значение внешнего диаметра. Чтобы распространить формулу на все ячейки я опять-таки воспользовался способом из второго пункта статьи.

Функция работает только с одним значением как искомым. Это не всегда удобно, потому что иногда нужно получить данные по двум и более значениям. В этом случае приходится сначала эти значения склеивать в одной ячейке, а уже потом делать по ним поиск.

То есть будь у меня задача по диаметрам тройника вернуть его длину магистрали, то я бы сначала склеил диаметры в одну ячейку, например в виде 400x300, а уже потом бы делал поиск.

5. Вернуть значение из двумерного массива

Статью пишу только из-за этой функции, на самом деле. Очень простое решение, но я его не знал.

Иногда данные по размерам выглядят так:

Данные по длинам перехода в зависимости от диаметров в виде таблицы Пифагора

Это и есть двумерный массив, условно есть данные по оси Х и оси У, на их пересечении — значение параметра. Но мне для таблицы выбора такое не подходит, мне нужно всё в виде одного столбца. Для этого использую две функции: ИНДЕКС и ПОИСКПОЗ. Индекс возвращает значение из массива «по координатам», то есть по индексам строки и столбца, а Поиск позиции возвращает эти самые индексы.

Перевожу данные в Эксель, обязательно нужно почистить таблицу и удалить всё, где нет нужных значений:

Данные для работы. Нужно вернуть длину перехода в столбец D

Полная формула выглядит так:

=ИНДЕКС($I$4:$AB$23; ПОИСКПОЗ(B2; $H$4:$H$23; 0); ПОИСКПОЗ(C2; $I$3:$AB$3; 0))

Разберу сначала функцию ПОИСКПОЗ. У нас есть список данных: для примера возьмём список из четырёх диаметров 15, 20, 25, 32. У каждого диаметра в списке есть своя позиция, то есть индекс. У 15 это 0, у 20 — 1, у 25 — 2, у 32 — 3. Программисты — жадные ребята, экономят на всём, поэтому даже нумерация у них с нуля.

Когда сын программиста в садике сообщает, что ему 4 года, то показывает 3 пальца. Потому что сжатый кулачок — это ноль и отсчёт начинается отсюда. Правдивая история.

Функция ПОИСКПОЗ получает на вход диаметр и список диаметров, а также способ поиска: меньше, больше или точное совпадение. Нам нужно точное совпадение. Возвращает функция индекс диаметра в списке диаметров. Подали 25 — получили 2. Подали 15 — получили 0.

Функция ИНДЕКС из заданного массива возвращает значение по его «координатам», то есть по индексу строки и столбца. Поэтому нужно подать массив данных и значения индексов. Индексы получаем функцией ПОИСКПОЗ, а массив просто выделяем в Экселе.

Разберём формулу по частям:

1 — это массив с длинами перехода. Значения диаметров сюда брать не надо, только длины. Знак $ блокирует адрес ячейки, чтобы при перемещении по столбцу массив был одним и тем же.

2 — в первую функцию ПОИСКПОЗ подаю ячейку со значением первого диаметра, у меня это больший диаметр перехода.

3 — так как сначала в функцию ИНДЕКС нужно подать индекс строки, то есть типа координату Y, то задаю диапазон для поиска — все значения диаметров, которые получил из каталога производителя.

Тут указываю этот диапазон диаметров

4 и 7 — указываю, что мне нужно точное совпадение.

5 — тоже самое, тут указываю меньший диаметр перехода.

6 — указываю диапазон со значениями меньшего диаметра, это в итоге будет координатой Х для поиска.

Тут указываю вот этот диапазон диаметров

В итоге получаю своё значение длины. Распространяю формулу на весь столбец. Ещё и функцией ВПР добавляю наружные диаметры, и таблица выбора для геометрии перехода готова. Потрясающе.

Хотите научу делать переходы? Я могу. Ниже ссылочка на мои видеокурсы по разработке семейств, пока все, кто учится, очень довольны, можете почитать отзывы.