Excel вывод текста в зависимости от данных
Зависимый выпадающий список в Excel и Google таблицах
20 июля 2021 г. 9555
Введение
Выпадающий список — это супер полезный инструмент, который способствует более комфортной работе с информацией. Он позволяет вместить в ячейку сразу несколько значений, с которыми можно работать, как и с любыми другими. Чтобы выбрать нужное, достаточно щелкнуть на значок стрелки, после чего отобразится перечень значений. После выбора конкретного, ячейка автоматически заполняется им.
Рассмотрим особенности создания выпадающих списков на примере:
Исходные данные:
Задача:
- Создать автоматически обновляемый выпадающий список уникальных городов
- На основе выбранного города, создать зависимый выпадающий список адресов
Мы будем двигаться поэтапно, уделяя внимание всем возможностям данного инструмента.
Рабочие файлы по ссылке ниже
Обзорное видео о работе с выпадающими списками в Excel и Google таблицах смотрите ниже. Приятного просмотра!
Как сделать выпадающий список в Excel?
Начнем с основ. Для того, чтобы создать выпадающий список потребуется список с данными и инструмент «Проверка данных».
Выбираем ячейку, в которой будем создавать выпадающий список. Далее переходим к инструменту «Проверка данных», тип данных – «Список». В поле «Источник» указываем диапазон списка.
Выпадающий список готов!
Такой способ позволяет представить обычный диапазон в виде выпадающего списка. Повторы данных остались в списке (в диапазоне A2:A16 названия городов повторяются и в выпадающем списке они также повторяются). Это, конечно, не удобно. О том, как сделать выпадающий список уникальных значений в Excel мы поговорим далее, пока остановимся на этом варианте.
Как создать зависимый выпадающий список в Excel?
Существует несколько вариантов. Один из них, это сочетание именованных диапазонов и функции ДВССЫЛ .
Именованный диапазон в Excel – это ячейка (или диапазон ячеек), которой присвоено имя.
Функция ДВССЫЛ в Excel преобразовывает текст в ссылку.
Способ 1: именованные диапазоны + функция ДВССЫЛ
Для начала создадим именованные диапазоны с адресами. Имя каждому присвоим в соответствии с городом.
Алгоритм создания именованного диапазона: выделяем диапазон, далее «Формулы» – «Задать имя».
У нас получится 5 именованных диапазона: Волгоград, Воронеж, Краснодар, Москва и Ростов_на_Дону.
Обратите внимание, к именам диапазонов есть список требований. Например, в имени не могут содержаться пробелы, запятые, дефисы и прочие символы. Подробнее о создании именованных диапазонов и работе с ними мы говорим в нашем бесплатном курсе Основы Excel.
Поэтому, вместо дефисов в названии города Ростов-на-Дону мы укажем допустимый символ – нижнее подчеркивание.
Именованные диапазоны готовы.
Теперь выбираем ячейку для второго выпадающего списка, того, который будет зависимым. Переходим к инструменту «Проверка данных», тип данных – «Список». В поле «Источник» указываем функцию: =ДВССЫЛ(D2) , где D2 – это адрес ячейки с первым выпадающим списком городов.
В ячейке D2, которая используется в качестве аргумента функции ДВССЫЛ , находится текстовое выражение, которое совпадает с именем соответствующего именованного диапазона с названиями городов. В результате функция возвращает ссылку на соответствующий именованный диапазон.
Зависимый выпадающий список адресов готов.
Меняя значения в ячейке D2, меняются списки в ячейке E2. За исключением города Ростов-на-Дону. В выпадающем списке городов (ячейка D2), в названии используется дефис, а в именованном диапазоне – нижнее подчеркивание.
Чтобы устранить это несоответствие, перед тем как применять функцию ДВССЫЛ , обработаем значения функцией ПОДСТАВИТЬ .
Функция ПОДСТАВИТЬ заменяет определенный текст в текстовой строке на новое значение. Вместо: =ДВССЫЛ(D2) укажем: =ДВССЫЛ(ПОДСТАВИТЬ(D2;"-";"_"))
То есть, мы проводим предварительную обработку значений, чтобы они соответствовали правилам написания имён. Если в названии города имеются дефисы, они будут заменены на нижнее подчеркивание.
Теперь зависимый выпадающий список работает и для города, содержащего в названии дефисы – Ростов-на-Дону. Вернемся к выпадающему списку городов.
Как автоматически обновить выпадающий список в Excel, при добавлении новых данных?
Для начала создадим из диапазона данных «умную» таблицу Excel. Сделать это можно сочетанием клавиш Ctrl+T .
Одним из полезных свойств умной таблицы является растягивающийся диапазон. То есть, если мы будем добавлять новые строки, они автоматически будут попадать в выпадающий список. Например, добавим новый город – Санкт-Петербург. И вот, он уже появился в нашем первом выпадающем списке.
Как сделать выпадающий список уникальных значений в Excel?
Надоело смотреть на повторяющиеся названия городов в выпадающем списке. Реализуем выпадающий список так, чтобы названия городов в нем не повторялись. Для этого, добавим слева вспомогательный столбец. Мы дали ему название – «Уникальные».
И включим новый столбец в диапазон «умной» таблицы. «Конструктор» – «Размер таблицы». Вместо =$B$1:$C$17 указываем: =$A$1:$C$17
Визуально видно, что диапазон «умной» таблицы Excel расширился. Включать этот столбец в диапазон таблицы необходимо для того, чтобы при добавлении новых данных, пересчет уникальных городов происходил автоматически.
В ячейку А2 добавим формулу массива, которая будет формировать список уникальных городов:
Чтобы Excel воспринял нашу формулу, как формулу массива, жмем Ctrl + Shift + Enter .
Получаем список уникальных городов, который при добавлении новых строк будет автоматически обновляться.
Из списка уникальных городов создадим именованный диапазон (мы назвали его - «Уникальные»), который затем используем в качестве источника для выпадающего списка городов.
«Проверка данных» – «Список». В источнике данных, вместо предыдущего диапазона с названиями городов =$B$2:$B$18 , задаем имя – =Уникальные
Как видим, список уникальных значений мы получили, но в придачу у нас остались совершенно ненужные пустые строки из таблицы.
Чтобы их убрать, доработаем именованный диапазон «Уникальные». В диспетчере имен, вместо диапазона =Таблица1[Уникальные] используем: =СМЕЩ(Лист1!$A$2;0;0;СЧЁТЗ(Таблица1[Уникальные])-СЧИТАТЬПУСТОТЫ(Таблица1[Уникальные]))
где: Лист1!$A$2 – ячейка со значением первого пункта списка уникальных значений
Таблица1[Уникальные] – столбец с перечнем всех пунктов списка
Выпадающий список уникальных автоматически обновляемых значений готов.
Вернемся к зависимому списку с адресами. Выпадающий список городов теперь динамический, а вот адреса так и остались фиксированными именованными диапазонами.
Как сделать автоматически обновляемый зависимый список? Способ 2: СМЕЩ+ПОИСКПОЗ+СЧЁТЕСЛИ
Именованные диапазоны, которые мы до этого использовали в сочетании с функцией ДВССЫЛ можно удалить, далее они нам не пригодятся. Рассмотрим способ создания зависимого, автоматически обновляемого выпадающего списка.
В ячейку F2 (зависимый выпадающий список адресов) вместо: =ДВССЫЛ(ПОДСТАВИТЬ(E2;"-";"_")) вставляем: =СМЕЩ($B$2;ПОИСКПОЗ(E2;$B$2:$B$18;0)-1;1;СЧЁТЕСЛИ($B$2:$B$18;E2);1)
Для корректной работы этого способа, данные в столбце с городом должны быть отсортированы. Функция СМЕЩ будет динамически ссылаться только на ячейки адресов определенного города.
Аргументы функции:
Ссылка – берем первую ячейку нашего списка, т.е. $B$2
Смещение по строкам – считает функция ПОИСКПОЗ , которая выдает порядковый номер ячейки с выбранным городом (E2) в заданном диапазоне ( $B$2:$B$18 )
Смещение по столбцам = 1, т.к. мы хотим сослаться на адреса в соседнем столбце (С)
Высота – вычисляем с помощью функции СЧЁТЕСЛИ , которая подсчитывает количество встретившихся в диапазоне ( $B$2:$B$18 ) нужных нам значений – названий городов (E2)
Ширина = 1, т.к. нам нужен один столбец с адресами
Готово! Добавляем новые данные, сортируем список и пользуемся зависимыми, автоматически обновляемыми выпадающими списками. При необходимости можно скопировать выпадающие списки на строки ниже, они будут корректно работать. При копировании выпадающих списков обращайте внимание на адрес ссылок. Абсолютные ссылки остаются неизменными при копировании, относительные – меняют адрес ячеек относительно нового места.
С выпадающими списками в Google таблицах все немного иначе.
Как сделать выпадающий список в Google таблицах?
В Google таблицах есть аналогичный инструмент для создания выпадающих списков – «Проверка данных».
Выделяем ячейку, в которой будем размещать выпадающий список.
«Данные» – «Настроить проверку данных» – «Значение из диапазона»
Важное отличие от проверки данных Excel в том, что инструмент «Проверка данных» в Google таблицах автоматически выдает уникальные значения, и значит, нам не придется создавать вспомогательный столбец с расчетами.
Как создать зависимый выпадающий список в Google таблицах?
Возвращаемся к двум основным способам, которые мы рассмотрели в Excel.
Способ 1: именованные диапазоны + ДВССЫЛ
Создадим именованные диапазоны с адресами. Имя каждому присвоим в соответствии с городом.
Выделяем ячейки – «Данные» – «Настроить именованные диапазоны»
Указываем имя и жмем готово. У нас получится 5 именованных диапазонов: Волгоград, Воронеж, Краснодар, Москва и Ростов_на_Дону.
Также, как и в Excel, в Google таблицах к именам диапазонов есть список требований.
Поэтому, вместо дефисов в названии города Ростов-на-Дону укажем допустимый символ – нижнее подчеркивание.
В Google таблицах мы не сможем подобно Excel задать функцию ДВССЫЛ в инструменте «Проверка данных». Поэтому, разместим результат функции ДВССЫЛ в пустых ячейках правее. Не забываем добавить обработку значений от дефисов функцией ПОДСТАВИТЬ. Подробнее о том, для чего это нужно, мы говорили ранее в примере Excel.
В ячейке F1 введем: =ДВССЫЛ(ПОДСТАВИТЬ(D2;"_";"-"))
Последний штрих в создании зависимого выпадающего списка, в разделе «Настроить проверку данных», в качестве диапазона указываем список из столбца F:F.
При дальнейшей работе вспомогательный столбец F можно скрыть. Минус такого метода – отсутствие динамичности. Если мы добавим новый город и адрес, то они не появятся в созданных выпадающих списках. Но это решаемо!
Как автоматически обновить выпадающий список в Google таблицах при добавлении новых данных?
В выпадающем списке городов, достаточно расширить диапазон и вместо =$A$2:$A$16 указать: =$A$2:$A . Теперь при добавлении нового города он автоматически появляется в выпадающем списке.
Как автоматически обновить зависимый выпадающий список в Google таблицах при добавлении новых данных?
Для того, чтобы зависимый выпадающий список автоматически обновлялся с добавлением новых данных, воспользуемся функцией СМЕЩ .
В ячейке G6 укажем:
Важно: для корректной работы этого способа, данные в столбце с городом должны быть отсортированы от А до Я, или от Я до А. Подробнее о том, как в данном случае работает функция СМЕЩ читайте выше в примере с Excel.
Заключительным этапом поместим результат функции СМЕЩ в диапазон выпадающего списка.
Скроем вспомогательные столбцы для удобства.
Работа выпадающих списков в Google таблицах хоть и схожа с Excel, но все же имеет свои отличительные особенности. Добавляем новые данные, сортируем список и пользуемся зависимыми, автоматически обновляемыми выпадающими списками.
Заключение
Теперь Вам известны несколько способов, как создать выпадающие списки в Excel и Google таблицах. Смотрите примеры и создавайте нужные Вам выпадающие списки.
Изучить работу в программе Excel Вы можете на наших курсах: Онлайн-курсы по Excel
Пройдите бесплатный тест на нашем сайте, чтобы объективно оценить свой уровень владения инструментами и функциями программы Excel: Пройти тест
Тип и формат данных в Excel и Google таблицах
21 июня 2021 г. 1938 MS Excel Video File Google Sheets
В этой статье мы рассмотрим стандартные варианты форматирования данных в ячейках, а также научимся создавать собственные форматы отображения данных.
Автоматическое разделение таблицы Excel на книги
30 марта 2021 г. 2092 MS Excel Code
В этой статье мы снова рассмотрим разделение таблицы на основе столбца, но только теперь данные будут делиться не на листы, а на отдельные книги Excel.
Расшифровка показателей анализа состава тела
27 июня 2021 г. 3535 Info Health
Подробная расшифровка показателей анализа состава тела. Важно правильно интерпретировать результаты для снижения рисков заболеваний и достижения результатов
Процесс анализа данных (Часть I)
6 июня 2021 г. 2145 Info Statistics Analytics
Серия статей, посвященных процессу анализа данных. В них мы расскажем о базовых принципах построения практического проекта по анализу данных. Первая часть посвящена подготовке данных и описательным статистикам.
У подавляющего большинства пользователей Excel при слове "фильтрация данных" в голове всплывает только обычный классический фильтр с вкладки Данные - Фильтр (Data - Filter) :
Такой фильтр - штука привычная, спору нет, и для большинства случаев вполне сойдет. Однако бывают ситуации, когда нужно проводить отбор по большому количеству сложных условий сразу по нескольким столбцам. Обычный фильтр тут не очень удобен и хочется чего-то помощнее. Таким инструментом может стать расширенный фильтр (advanced filter), особенно с небольшой "доработкой напильником" (по традиции).
Основа
Для начала вставьте над вашей таблицей с данными несколько пустых строк и скопируйте туда шапку таблицы - это будет диапазон с условиями (выделен для наглядности желтым):
Между желтыми ячейками и исходной таблицей обязательно должна быть хотя бы одна пустая строка.
Именно в желтые ячейки нужно ввести критерии (условия), по которым потом будет произведена фильтрация. Например, если нужно отобрать бананы в московский "Ашан" в III квартале, то условия будут выглядеть так:
Чтобы выполнить фильтрацию выделите любую ячейку диапазона с исходными данными, откройте вкладку Данные и нажмите кнопку Дополнительно (Data - Advanced) . В открывшемся окне должен быть уже автоматически введен диапазон с данными и нам останется только указать диапазон условий, т.е. A1:I2:
Обратите внимание, что диапазон условий нельзя выделять "с запасом", т.е. нельзя выделять лишние пустые желтые строки, т.к. пустая ячейка в диапазоне условий воспринимается Excel как отсутствие критерия, а целая пустая строка - как просьба вывести все данные без разбора.
Переключатель Скопировать результат в другое место позволит фильтровать список не прямо тут же, на этом листе (как обычным фильтром), а выгрузить отобранные строки в другой диапазон, который тогда нужно будет указать в поле Поместить результат в диапазон. В данном случае мы эту функцию не используем, оставляем Фильтровать список на месте и жмем ОК. Отобранные строки отобразятся на листе:
Добавляем макрос
"Ну и где же тут удобство?" - спросите вы и будете правы. Мало того, что нужно руками вводить условия в желтые ячейки, так еще и открывать диалоговое окно, вводить туда диапазоны, жать ОК. Грустно, согласен! Но "все меняется, когда приходят они ©" - макросы!
Работу с расширенным фильтром можно в разы ускорить и упростить с помощью простого макроса, который будет автоматически запускать расширенный фильтр при вводе условий, т.е. изменении любой желтой ячейки. Щелкните правой кнопкой мыши по ярлычку текущего листа и выберите команду Исходный текст (Source Code) . В открывшееся окно скопируйте и вставьте вот такой код:
Эта процедура будет автоматически запускаться при изменении любой ячейки на текущем листе. Если адрес измененной ячейки попадает в желтый диапазон (A2:I5), то данный макрос снимает все фильтры (если они были) и заново применяет расширенный фильтр к таблице исходных данных, начинающейся с А7, т.е. все будет фильтроваться мгновенно, сразу после ввода очередного условия:
Так все гораздо лучше, правда? :)
Реализация сложных запросов
Теперь, когда все фильтруется "на лету", можно немного углубиться в нюансы и разобрать механизмы более сложных запросов в расширенном фильтре. Помимо ввода точных совпадений, в диапазоне условий можно использовать различные символы подстановки (* и ?) и знаки математических неравенств для реализации приблизительного поиска. Регистр символов роли не играет. Для наглядности я свел все возможные варианты в таблицу:
Критерий | Результат |
гр* или гр | все ячейки начинающиеся с Гр , т.е. Груша, Грейпфрут, Гранат и т.д. |
=лук | все ячейки именно и только со словом Лук, т.е. точное совпадение |
*лив* или *лив | ячейки содержащие лив как подстроку, т.е. Оливки, Ливер, Залив и т.д. |
=п*в | слова начинающиеся с П и заканчивающиеся на В т.е. Павлов, Петров и т.д. |
а*с | слова начинающиеся с А и содержащие далее С , т.е. Апельсин, Ананас, Асаи и т.д. |
=*с | слова оканчивающиеся на С |
=. | все ячейки с текстом из 4 символов (букв или цифр, включая пробелы) |
=м. н | все ячейки с текстом из 8 символов, начинающиеся на М и заканчивающиеся на Н , т.е. Мандарин, Мангостин и т.д. |
=*н??а | все слова оканчивающиеся на А , где 4-я с конца буква Н , т.е. Брусника, Заноза и т.д. |
>=э | все слова, начинающиеся с Э , Ю или Я |
<>*о* | все слова, не содержащие букву О |
<>*вич | все слова, кроме заканчивающихся на вич (например, фильтр женщин по отчеству) |
= | все пустые ячейки |
<> | все непустые ячейки |
>=5000 | все ячейки со значением больше или равно 5000 |
5 или =5 | все ячейки со значением 5 |
>=3/18/2013 | все ячейки с датой позже 18 марта 2013 (включительно) |
- Знак * подразумевает под собой любое количество любых символов, а ? - один любой символ.
- Логика в обработке текстовых и числовых запросов немного разная. Так, например, ячейка условия с числом 5 не означает поиск всех чисел, начинающихся с пяти, но ячейка условия с буквой Б равносильна Б*, т.е. будет искать любой текст, начинающийся с буквы Б.
- Если текстовый запрос не начинается со знака =, то в конце можно мысленно ставить *.
- Даты надо вводить в штатовском формате месяц-день-год и через дробь (даже если у вас русский Excel и региональные настройки).
Логические связки И-ИЛИ
Условия записанные в разных ячейках, но в одной строке - считаются связанными между собой логическим оператором И (AND) :
Т.е. фильтруй мне бананы именно в третьем квартале, именно по Москве и при этом из "Ашана".
Если нужно связать условия логическим оператором ИЛИ (OR) , то их надо просто вводить в разные строки. Например, если нам нужно найти все заказы менеджера Волиной по московским персикам и все заказы по луку в третьем квартале по Самаре, то это можно задать в диапазоне условий следующим образом:
Если же нужно наложить два или более условий на один столбец, то можно просто продублировать заголовок столбца в диапазоне критериев и вписать под него второе, третье и т.д. условия. Вот так, например, можно отобрать все сделки с марта по май:
В общем и целом, после "доработки напильником" из расширенного фильтра выходит вполне себе приличный инструмент, местами не хуже классического автофильтра.
Автоматизация заполнения и вывода файлов по шаблонам рутинных документов это одна из та областей в отрасли строительства по которой традиционно софт, кроме бухгалтерского, находится на уровне вылизанных поделок, на мой скромный взгляд. Поэтому, развивая тему, приглашаю обсудить те проблемы и возможности, с которыми пришлось столкнуться в процессе реализации на базе MS Excel.
Со времени предыдущей статьи прошло уже пол года. За это время при помощи этой заготовки была разработана текстовая часть Исполнительной документации и сдана Заказчику. По итогам работы и отзывам редких участников в файл были внесены следующие правки, о которых я бы хотел поговорить и это 3 большие темы:
- Эстетика и юзабилити
- Оптимизация кода + нововведения
- Структура и связи
1. Эстетика и юзабилити
— Таблицы это в первую очередь таблицы, безликие ячейки с подписанными колонками и строками. Однако очень часто мы сталкиваемся с ситуацией, когда необходимы дополнительные пояснения к значению, которое будет находится в ячейке, или требуется дополнительно активизировать внимание пользователя на важности вводимого значения. Особенно важно, если у Вас, как в моем случае, строки в колонке очень длинной таблицы содержат разноплановую информацию, например: даты, виды работ, материалы, подписанты и многие др. В таких случаях у нас есть 2 инструмента для решения задачи:
Есть и минусы такого решения, в частности всплывающие подсказки могут раздражать, но в ситуации, когда на объекте 15" мониторы на ноутбуках с разрешением 1366×768 это разумный компромисс, что бы рабочая область была как можно больше.
Если внимательно проанализировать данные, то окажется что в таблице будут ячейки 3х типов:
- ячейки в которые непосредственно необходимо вводить новую текстовую информацию;
- ячейки, значение которых может принимать значение из ограниченного диапазона, введенного заранее, например: ФИО и должность подписантов;
- ячейки в которых прописаны формулы, например есть часть данных которая будет повторяться из акта в акт и такую информацию достаточно ввести один раз, например: наименование объекта, участок, организация и т.п.; либо формулы призванные реализовать технические возможности, например: переноса строки, подтягивание объемов работ, регалий по ФИО и т.п.
Здесь первая процедура постоянно будет защищать лист при помощи пароля 111, вторая будет блокировать функционал вырезать-вставить. Надо ли говорить, что это все работает только при включенных макросах, но с другой стороны без них и файл на 100% функционировать не будет.
Для случаев же п.2 разумно завести лист где столбцы будут содержать меняющиеся значения, прописать в них ссылки на диапазоны, присвоить им имена, т.е. на вкладке «Формулы» -> «Диспетчер имен» каждому диапазону присвоить имена и через вкладку «Данные» -> пункт меню «Проверка данных» -> вкладка «Параметры» -> условие проверки — «Список» реализовать выпадающее меню.
И, конечно, не забывайте ставить условия форматирования цветом, например для случаев, когда заполнены все необходимые строки в столбце через «Условное форматирование», например формула условного форматирования закрашивает ячейку, если следующие ячейки под ней содержат текст: =И(ДЛСТР(E5)>0; ДЛСТР(E6)>0)
2. Оптимизация кода + нововведения
Начать придется издалека, а именно вернуться к вопросу о реализации механизма заполнения шаблона. Если Вы решите заполнить шаблон в формате Excel и в формате Word, то это будут совершенно 2 разных механизма. В основе своей в файл Excel пишутся значения в конкретные ячейки файла или диапазоны ячеек и имеют привязку вида (у, х) (не спрашивайте почему у них строка идет впереди столбца при адресации — не знаю), например: Worksheet.Cells(y, x) = k. Отсюда же и первая мысль, что заполнять Excel-шаблон можно либо явным образом, т.е. непосредственно весь макрос будет содержать что откуда берется и куда закладывается, но что если придется вносить изменения в таблицы данных или выйдет новая форма шаблона? Отсюда вторая идея реализации, код которой описан в первой статье — это парсинг некоторых символов, которыми сперва заполняется массив, а так же в свою очередь содержит файл шаблона в нужных местах. Затем в каждой строке шаблона ищется совпадение с элементами массива поочередно, если совпадение есть, то порядковый номер массива привязан к строке таблицы откуда берутся данные, а столбец берется с листа в котором мы указываем какие именно акты мы хотим вывести. Итого несколько вложенных циклов, что накладывает ограничения на форматирование шаблона Excel, чем проще — тем лучше, потому что чем больше ячеек парсить — тем дольше будет происходить заполнение шаблона данными.
По многочисленным просьбам мною была интегрирована возможность вывода в шаблон формата Word, и здесь на самом деле есть 2 способа вывода текста:
когда мы так же считываем массив управляющих кодов, вручную прописываем их в шаблоне через «Вставка» -> «Закладки» и дальше просто прогоняем макросом присваивая закладке данные из соответствующей ей ячейке в файле Excel.
Здесь вынесена в отдельную процедуру обращение к закладке и arrСсылкиДанных(i) — это массив который содержит управляющие символы. Издержки метода, если Вам потребуется сослаться на значение Закладки в другом месте, например дату нужно использовать в заголовке и напротив фамилии каждого подписанта, то необходимо использовать в шаблоне Меню «Вставка» -> пункт меню «Перекрестная ссылка» -> Тип ссылки: «Закладка», Вставить ссылку на: «Текст закладки» и снять галочку «Вставить как гиперссылку». Что бы это отобрадзилось корректно не забудте обновить в конце макроса перед выводом поля Wd.Fields.Update
Здесь нужно обратить внимание, что у каждой таблицы в Word есть свой внутренний номер, методом нехитрого перебора Вы найдете нужный, а дальше принцип тот же, что и в Excel.
Между выводами в файлы форматов Word и Excel есть огромная пропасть, которая заключается в следующем:
Шаблон Excel требует перед использованием настроить отображение под конкретный принтер, т.к. фактическая область печати разнится от модели к модели. Так же перенос строки текста возможен, но только в пределах ячейки/объединенных ячеек. В последнем случае не будте автораздвигания строки, в случае переноса текста. Т.е. Вам вручную придется заранее определит границы области, которые будут содержать текст, который в свою очередь в них еще должен убраться. Зато Вы точно задали границы печати и выводимого текста и уверены, что не съедет информация (но не содержание) с одного листа на другой.
Шаблон Word при настройке автоматически переносит текст на последующую строку, если он не убрался по ширине ячейки/строки, однако этим самым он вызывает непрогнозируемый сдвиг текста по вертикали. Учитывая тот факт, что по требованиям к Исполнительной документации в строительстве ЗАПРЕЩЕНО один акт печатать на 2х и более листах, то это в свою очередь так же рождает проблемы.
Вторым большим нововведением стал отказ от реализации переноса текстовых строк с макроса VBA и заменой на функцию Excel, благодаря чему ускорилась работа с файлом.
Для первой строки:
<=ЕСЛИОШИБКА(ЕСЛИ($F$20<>"-"; ПСТР('Данные для проекта'!$C$3; СУММ(ДЛСТР(F$1:F1))+1;105-ПОИСКПОЗ(" *"; ПРАВСИМВ(ПСТР('Данные для проекта'!$C$3; СУММ(ДЛСТР(F$1:F1))+1;105); СТРОКА($1:$10));)));"-")>
Здесь используется принцип массивов, т.е. вводится такой текст по Ctrl + Shift + Enter, а не обычному Enter. Сами формулы располагаются в ячейках F1 и F2. 'Данные для проекта'!$C$3 — ссылка на наименования объекта, длина текста которого более 105 символов. Перенос организуется в случае превышения длины текста в 105 символов.
Еще одним нововведением стал общий реестр, а так же контроль списания материалов по актам АОСР, но здесь ничего нового, просто парсинг соответствующих строк в свяске ИНДЕКС + ПОИСКПОЗ, которые расписаны во многих мануалах.
3. Структура и связи
Но мой пост так бы и остался рядовым постом с очередной игрой в изобретание велосипеда инструментами, которые рассчитаны на совершенно другое, если бы ни одно НО(!) Месячно-суточный график.
Идея о том, что можно именно на него много чего повесить, например заполнение Общего журнала работ в части Раздела 3 — наименование работ по датам, очередность и необходимость Актов освидетельствования скрытых работ и не только — завладела моими мыслями. Обычно в Excel закрашивают даты, в зависимости от диапазонов дат — начало и конец, но не на стройке. На стройке в календарном графике пишут объемы, а в зависимости от того с какой даты напротив наименования работ стоят объемы и по которую — получаются диапазоны дат отчетных периодов. На скриншоте серым помечены объемы попадающие в систематизированные отчетные периоды (1мес). Таким образом получается, что если:
Для удобства работы с текстом в Excel существуют текстовые функции. Они облегчают обработку сразу сотен строк. Рассмотрим некоторые из них на примерах.
Примеры функции ТЕКСТ в Excel
Преобразует числа в текст. Синтаксис: значение (числовое или ссылка на ячейку с формулой, дающей в результате число); формат (для отображения числа в виде текста).
Самая полезная возможность функции ТЕКСТ – форматирование числовых данных для объединения с текстовыми данными. Без использования функции Excel «не понимает», как показывать числа, и преобразует их в базовый формат.
Покажем на примере. Допустим, нужно объединить текст в строках и числовые значения:
Использование амперсанда без функции ТЕКСТ дает «неадекватный» результат:
Excel вернул порядковый номер для даты и общий формат вместо денежного. Чтобы избежать подобного результата, применяется функция ТЕКСТ. Она форматирует значения по заданию пользователя.
Формула «для даты» теперь выглядит так:
Второй аргумент функции – формат. Где брать строку формата? Щелкаем правой кнопкой мыши по ячейке со значением. Нажимаем «Формат ячеек». В открывшемся окне выбираем «все форматы». Копируем нужный в строке «Тип». Вставляем скопированное значение в формулу.
Приведем еще пример, где может быть полезна данная функция. Добавим нули в начале числа. Если ввести вручную, Excel их удалит. Поэтому введем формулу:
Если нужно вернуть прежние числовые значения (без нулей), то используем оператор «--»:
Обратите внимание, что значения теперь отображаются в числовом формате.
Функция разделения текста в Excel
Отдельные текстовые функции и их комбинации позволяют распределить слова из одной ячейки в отдельные ячейки:
- ЛЕВСИМВ (текст; кол-во знаков) – отображает заданное число знаков с начала ячейки;
- ПРАВСИМВ (текст; кол-во знаков) – возвращает заданное количество знаков с конца ячейки;
- ПОИСК (искомый текст; диапазон для поиска; начальная позиция) – показывает позицию первого появления искомого знака или строки при просмотре слева направо
При разделении текста в строке учитывается положение каждого знака. Пробелы показывают начало или конец искомого имени.
Распределим с помощью функций имя, фамилию и отчество в разные столбцы.
В первой строке есть только имя и фамилия, разделенные пробелом. Формула для извлечения имени: =ЛЕВСИМВ(A2;ПОИСК(" ";A2;1)). Для определения второго аргумента функции ЛЕВСИМВ – количества знаков – используется функция ПОИСК. Она находит пробел в ячейке А2, начиная слева.
Формула для извлечения фамилии:
С помощью функции ПОИСК Excel определяет количество знаков для функции ПРАВСИМВ. Функция ДЛСТР «считает» общую длину текста. Затем отнимается количество знаков до первого пробела (найденное ПОИСКом).
Вторая строка содержит имя, отчество и фамилию. Для имени используем такую же формулу:
Формула для извлечения фамилии несколько иная: Это пять знаков справа. Вложенные функции ПОИСК ищут второй и третий пробелы в строке. ПОИСК(" ";A3;1) находит первый пробел слева (перед отчеством). К найденному результату добавляем единицу (+1). Получаем ту позицию, с которой будем искать второй пробел.
Часть формулы – ПОИСК(" ";A3;ПОИСК(" ";A3;1)+1) – находит второй пробел. Это будет конечная позиция отчества.
Далее из общей длины строки отнимается количество знаков с начала строки до второго пробела. Результат – число символов справа, которые нужно вернуть.
Формула «для отчества» строится по тем же принципам:
Функция объединения текста в Excel
Для объединения значений из нескольких ячеек в одну строку используется оператор амперсанд (&) или функция СЦЕПИТЬ.
Например, значения расположены в разных столбцах (ячейках):
Ставим курсор в ячейку, где будут находиться объединенные три значения. Вводим равно. Выбираем первую ячейку с текстом и нажимаем на клавиатуре &. Затем – знак пробела, заключенный в кавычки (“ “). Снова - &. И так последовательно соединяем ячейки с текстом и пробелы.
Получаем в одной ячейке объединенные значения:
Использование функции СЦЕПИТЬ:
С помощью кавычек в формуле можно добавить в конечное выражение любой знак или текст.
Функция ПОИСК текста в Excel
Функция ПОИСК возвращает начальную позицию искомого текста (без учета регистра). Например:
Функция ПОИСК вернула позицию 10, т.к. слово «Захар» начинается с десятого символа в строке. Где это может пригодиться?
Функция ПОИСК определяет положение знака в текстовой строке. А функция ПСТР возвращает текстовые значения (см. пример выше). Либо можно заменить найденный текст посредством функции ЗАМЕНИТЬ.
Условное форматирование – удобный инструмент для анализа данных и наглядного представления результатов. Умение им пользоваться сэкономит массу времени и сил. Достаточно бегло взглянуть на документ – нужная информация получена.
Как сделать условное форматирование в Excel
Инструмент «Условное форматирование» находится на главной странице в разделе «Стили».
При нажатии на стрелочку справа открывается меню для условий форматирования.
Сравним числовые значения в диапазоне Excel с числовой константой. Чаще всего используются правила «больше / меньше / равно / между». Поэтому они вынесены в меню «Правила выделения ячеек».
Введем в диапазон А1:А11 ряд чисел:
Выделим диапазон значений. Открываем меню «Условного форматирования». Выбираем «Правила выделения ячеек». Зададим условие, например, «больше».
Введем в левое поле число 15. В правое – способ выделения значений, соответствующих заданному условию: «больше 15». Сразу виден результат:
Выходим из меню нажатием кнопки ОК.
Условное форматирование по значению другой ячейки
Сравним значения диапазона А1:А11 с числом в ячейке В2. Введем в нее цифру 20.
Выделяем исходный диапазон и открываем окно инструмента «Условное форматирование» (ниже сокращенно упоминается «УФ»). Для данного примера применим условие «меньше» («Правила выделения ячеек» - «Меньше»).
В левое поле вводим ссылку на ячейку В2 (щелкаем мышью по этой ячейке – ее имя появится автоматически). По умолчанию – абсолютную.
Результат форматирования сразу виден на листе Excel.
Значения диапазона А1:А11, которые меньше значения ячейки В2, залиты выбранным фоном.
Зададим условие форматирования: сравнить значения ячеек в разных диапазонах и показать одинаковые. Сравнивать будем столбец А1:А11 со столбцом В1:В11.
Выделим исходный диапазон (А1:А11). Нажмем «УФ» - «Правила выделения ячеек» - «Равно». В левом поле – ссылка на ячейку В1. Ссылка должна быть СМЕШАННАЯ или ОТНОСИТЕЛЬНАЯ! , а не абсолютная.
Каждое значение в столбце А программа сравнила с соответствующим значением в столбце В. Одинаковые значения выделены цветом.
Внимание! При использовании относительных ссылок нужно следить, какая ячейка была активна в момент вызова инструмента «Условного формата». Так как именно к активной ячейке «привязывается» ссылка в условии.
В нашем примере в момент вызова инструмента была активна ячейка А1. Ссылка $B1. Следовательно, Excel сравнивает значение ячейки А1 со значением В1. Если бы мы выделяли столбец не сверху вниз, а снизу вверх, то активной была бы ячейка А11. И программа сравнивала бы В1 с А11.
Чтобы инструмент «Условное форматирование» правильно выполнил задачу, следите за этим моментом.
Проверить правильность заданного условия можно следующим образом:
- Выделите первую ячейку диапазона с условным форматированим.
- Откройте меню инструмента, нажмите «Управление правилами».
В открывшемся окне видно, какое правило и к какому диапазону применяется.
Условное форматирование – несколько условий
Исходный диапазон – А1:А11. Необходимо выделить красным числа, которые больше 6. Зеленым – больше 10. Желтым – больше 20.
- 1 способ. Выделяем диапазон А1:А11. Применяем к нему «Условное форматирование». «Правила выделения ячеек» - «Больше». В левое поле вводим число 6. В правом – «красная заливка». ОК. Снова выделяем диапазон А1:А11. Задаем условие форматирования «больше 10», способ – «заливка зеленым». По такому же принципу «заливаем» желтым числа больше 20.
- 2 способ. В меню инструмента «Условное форматирование выбираем «Создать правило».
Заполняем параметры форматирования по первому условию:
Нажимаем ОК. Аналогично задаем второе и третье условие форматирования.
Обратите внимание: значения некоторых ячеек соответствуют одновременно двум и более условиям. Приоритет обработки зависит от порядка перечисления правил в «Диспетчере»-«Управление правилами».
То есть к числу 24, которое одновременно больше 6, 10 и 20, применяется условие «=$А1>20» (первое в списке).
Условное форматирование даты в Excel
Выделяем диапазон с датами.
Применим к нему «УФ» - «Дата».
В открывшемся окне появляется перечень доступных условий (правил):
Выбираем нужное (например, за последние 7 дней) и жмем ОК.
Красным цветом выделены ячейки с датами последней недели (дата написания статьи – 02.02.2016).
Условное форматирование в Excel с использованием формул
Если стандартных правил недостаточно, пользователь может применить формулу. Практически любую: возможности данного инструмента безграничны. Рассмотрим простой вариант.
Есть столбец с числами. Необходимо выделить цветом ячейки с четными. Используем формулу: =ОСТАТ($А1;2)=0.
Выделяем диапазон с числами – открываем меню «Условного форматирования». Выбираем «Создать правило». Нажимаем «Использовать формулу для определения форматируемых ячеек». Заполняем следующим образом:
Для закрытия окна и отображения результата – ОК.
Условное форматирование строки по значению ячейки
Задача: выделить цветом строку, содержащую ячейку с определенным значением.
Таблица для примера:
Необходимо выделить красным цветом информацию по проекту, который находится еще в работе («Р»). Зеленым – завершен («З»).
Выделяем диапазон со значениями таблицы. Нажимаем «УФ» - «Создать правило». Тип правила – формула. Применим функцию ЕСЛИ.
Порядок заполнения условий для форматирования «завершенных проектов»:
Обратите внимание: ссылки на строку – абсолютные, на ячейку – смешанная («закрепили» только столбец).
Аналогично задаем правила форматирования для незавершенных проектов.
В «Диспетчере» условия выглядят так:
Когда заданы параметры форматирования для всего диапазона, условие будет выполняться одновременно с заполнением ячеек. К примеру, «завершим» проект Димитровой за 28.01 – поставим вместо «Р» «З».
«Раскраска» автоматически поменялась. Стандартными средствами Excel к таким результатам пришлось бы долго идти.
Читайте также: