Расскажу про разные функции и формулы в Экселе, которыми пользуюсь при формировании таблиц выбора. Не всегда данные поступают в удобном виде, иногда их приходится форматировать или создавать заново. И тут пригодятся широкие возможности Экселя. Пойдём от совсем простого к более сложному.
1. Объединение значений
В отличие от Ревита у Экселя нет проблем с суммированием разных значений. Это здорово выручает, когда нужно сформировать наименование или маркировку элемента: объединяем значения из разных столбцов и готово.
Сделать это можно двумя путями: либо специальной функцией СЦЕПИТЬ, либо соединением ячеек и текста через символ &.
Если нужно добавить текст или разделитель, то пишите его в кавычках.
2. Распространение формулы на весь столбец
Скорее всего, все это и так знают, один я, как лошара, узнал только в этом году. Раньше писал формулу в ячейке, а потом тащил за правый нижний край ячейки до самого конца таблицы. Руками. Даже если таблица очень большая.
Потом узнал, что всё куда проще: достаточно дважды кликнуть на нижний правый угол таблицы и вуаля — формула распространится на весь столбец.
3. Заполнить пустые ячейки повторяющимися значениями
Открываете какой-нибудь ГОСТ или сайт, там таблица с диаметрами, но значения объединены, чтобы глазами было проще читать и меньше набирать текста. Глазам это, может, и удобно, а вот машиночитаемость такой таблицы равна нулю. Приходится копировать её и вставлять в Эксель для обработки.
После вставки получается что-нибудь вот такое:
Важный момент — в первой строке все значения должны быть заполнены.
Допустим, что таблицу почистили, всё настроили и проверили, что на самом верху всегда то значение, которое должно повторяться до тех пор, пока не появится новое. Например, в третьем столбце значение 273 будет повторяться в столбце до тех пор, пока не начинается новое значение 325.
Алгоритм такой: выделяем таблицу → нажимаем F5 → в окошке жмём «Выделить» → пустые ячейки → ОК → нажимаем F2 (команда ставит курсор в ячейку) и пишем знак равно = → выделяем ячейку выше, в которой есть значение → Ctrl + Enter.
Метод классный, но нужно тщательно готовить данные. Бывает, что после копирования и вставки таких таблиц значение из объединённой ячейки при отмене объединения оказывается не наверху, а в середине таблицы. В таком случае значения раскопируются неправильно. Поэтому будьте внимательны.
Даже с учётом такой подготовки алгоритм экономит время. Без него я бы не сделал свою библиотеку фланцев.
4. Вернуть значение из таблицы по ключу
Заголовок непонятный, поэтому опишу на примере. Есть список условных диаметров труб и их наружных диаметров. Составляю таблицу выбора для тройников, в неё нужно добавить наружные диаметры, чтобы при построении геометрии внешние диаметры тройника были такими же, как у трубы. Ну вот надо мне так и всё.
Тут нам поможет чудесная функция ВПР — вертикальный просмотр. Это что-то типа формулы для таблицы выбора в Ревите, о которых можете почитать большую статью, но попроще, так как работает только с одним аргументом.
Смотрим на примере. Вот заготовка таблицы, вписывать руками не хочется, тут у меня 24 с лишним тысячи строк:
В параметры D1 и D2 надо вписать наружные диаметры соответственно для магистрали DN1 и для ответвления DN2. Пишу формулу с ВПР. Синтаксис такой:
= ВПР(Искомое значение; Таблица для поиска; Номер столбца, откуда берём значение; Интервальный просмотр)
- Искомое значение — это номинальный диаметр стороны тройника.
- Таблица для поиска — два столбца с номинальными и внешними диаметрами трубы.
- Номер столбца — номер столбца в таблице для поиска, откуда возвращаем значение, в нашем случае в таблице два столбца, внешний диаметр во втором столбце.
- Интервальный просмотр — без понятия, что это, всегда ставлю ЛОЖЬ, чтобы искал точное совпадение.
Эксель берёт диаметр тройника, ищет его в таблице номинальных и внешних диаметров, находит в таблице такой же номинальный диаметр, как у тройника и возвращает значение внешнего диаметра. Чтобы распространить формулу на все ячейки я опять-таки воспользовался способом из второго пункта статьи.
Функция работает только с одним значением как искомым. Это не всегда удобно, потому что иногда нужно получить данные по двум и более значениям. В этом случае приходится сначала эти значения склеивать в одной ячейке, а уже потом делать по ним поиск.
То есть будь у меня задача по диаметрам тройника вернуть его длину магистрали, то я бы сначала склеил диаметры в одну ячейку, например в виде 400x300, а уже потом бы делал поиск.
5. Вернуть значение из двумерного массива
Статью пишу только из-за этой функции, на самом деле. Очень простое решение, но я его не знал.
Иногда данные по размерам выглядят так:
Это и есть двумерный массив, условно есть данные по оси Х и оси У, на их пересечении — значение параметра. Но мне для таблицы выбора такое не подходит, мне нужно всё в виде одного столбца. Для этого использую две функции: ИНДЕКС и ПОИСКПОЗ. Индекс возвращает значение из массива «по координатам», то есть по индексам строки и столбца, а Поиск позиции возвращает эти самые индексы.
Перевожу данные в Эксель, обязательно нужно почистить таблицу и удалить всё, где нет нужных значений:
Полная формула выглядит так:
=ИНДЕКС($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 — указываю диапазон со значениями меньшего диаметра, это в итоге будет координатой Х для поиска.
В итоге получаю своё значение длины. Распространяю формулу на весь столбец. Ещё и функцией ВПР добавляю наружные диаметры, и таблица выбора для геометрии перехода готова. Потрясающе.
Хотите научу делать переходы? Я могу. Ниже ссылочка на мои видеокурсы по разработке семейств, пока все, кто учится, очень довольны, можете почитать отзывы.