Создание зависимых списков.

Ответ на вопрос нашего подписчика. Чтобы создать второй раскрывающийся список, зависящий от значения выбранного в первом списке, воспользуемся Проверкой данных, только в Источнике данных укажем функцию =ДВССЫЛ().

#списки #зависимыесписки #проверкаданных

​Ввод значений, не превышающих суммы

В примере представлен простой рабочий лист с расчетом бюджета, составляющие которого лежат в диапазоне ячеек B1:B6. Запланированный бюджет хранится в ячейке E1. В момент, когда пользователь хочет изменить значение в любой из ячеек в диапазоне B1:B6, в случае если сумма (значение в ячейке E2) превысит установленный бюджет, пользователю будет выведено окно с ошибкой. Следующая формула проверки данных будет контролировать, чтобы сумма составляющих не превышала бюджета:


#ПроверкаДанных

​Ввод текста, начинающегося с определенного символа

В следующей формуле используется прием, который позволяет проводить проверку по заданному символу. В данном случае формула вернет значение ИСТИНА, если ввести в ячейку строку, которая будет начинаться с буквы А (независимо от регистра).


Это логическая формула, возвращающая значение истина в том случае, если первым символом ячейки является буква А. В любом другом случае эта формула возвращает значение ЛОЖЬ. При вводе этой формулы предполагается, что активной ячейкой выделенного диапазона является ячейка А1.

Следующая формула является вариантом предыдущей формулы. В ней используются групповые символы во втором аргументе функции СЧЁТЕСЛИ(). В этом случае формула проверки данных гарантирует, что содержимое ячейки начинается с буквы А и содержит ровно пять символов:


#ПроверкаДанных

​Вывод сообщения с напоминанием

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

В примере представлено сообщение, напоминающее пользователю о необходимости завершить предыдущий этап работы. В этом случае в диалоговом окне Проверка вводимых значений в списке Тип данных на вкладке Параметры выбирается вариант Любое значение, что оставляет пользователю возможность вводить в эту ячейку любые данные. Фактически это сообщение - просто напоминание, связанное с той ячейкой, которой пользователь непременно воспользуется в самом начале работы с данной рабочей книгой.

#ПроверкаДанных

​Структурирование рабочих листов

Если вы работали когда-нибудь с текстовым процессором, то наверняка знаете, что такое структура. Большинство текстовых процессоров (таких, как, например, Microsoft Word) поддерживает режим структуры, который позволяет просмотреть только заголовки и подзаголовки документа. Однако вы можете легко расширить структуру так, чтобы, кроме заголовков, увидеть расположенные под ними элементы (т.е. текст). В этом режиме очень удобно просматривать и анализировать структуру документов.
В Excel также можно использовать структуру, что значительно облегчает работу с большими массивами данных на рабочих листах.

Работая со структурами, нужно учитывать следующее:
▪️ Один рабочий лист может иметь только одну структуру. Если нужно создать несколько структур (на основе разных иерархий данных), для создания каждой из них используйте отдельный рабочий лист.
▪️ Можно создать структуру вручную или позволить Excel сделать это автоматически. Выбирая второй вариант, следует заранее подготовить таблицу данных, чтобы она была правильно отформатирована
▪️ Можно создать структуру для всех данных рабочего листа или только для выбранного диапазона данных.
▪️ Удалить структуру можно с помощью всего лишь одной команды: Данные => Структура => Разгруппировать => Удалить структуру.
▪️ Для освобождения пространства экрана символы структуры можно скрыть, оставив при этом саму структуру.
▪️ Структура может иметь до восьми вложенных уровней.

#Структурирование

​Подготовка данных

Данные какого типа пригодны для создания структуры? Как правило, данные должны иметь иерархическую организацию, примером которой может служить бюджет, построенный по следующей схеме:

  Офисы и филиалы
    Отдел
      Категория бюджета
        Статья бюджета

В данном случае каждая статья бюджета (например, Доходы от реализации товаров и услуг и Доходы от прочей деятельности) является частью некоторой категории бюджета (например, Доходы). Каждый отдел имеет собственный бюджет, подразделения состоят из отделов. Офисы и филиалы, в свою очередь, составляют фирму в целом. Такой тип организации данных подходит для создания вертикальной структуры (по строкам).
Создав структуру, можно просмотреть информацию на любом из ее уровней. Структура часто используется при создании отчетов для представления руководителям разных уровней. Так, высшему руководству фирмы представляются только итоги по подразделениям, руководителям подразделений - итоги по отделам, а каждому руководителю отдела - все данные по его отделу.
Следует помнить, что с помощью структур нельзя скрыть данные. Данные, скрытые при сворачивании структуры, могут быть легко обнаружены при ее разворачивании.
Данные, зависящие от времени, которые могут быть "свернуты" в более длительные временные интервалы (например, кварталы), подходят для создания горизонтальной структуры (по столбцам). Принципы работы с вертикальной и горизонтальной структурами - одни и те же, причем уровни последней не обязательно должны быть связаны со временем.
Прежде чем создавать структуру, следует убедиться в том, что все итоговые формулы введены правильно и согласованно. Согласованность означает, что все формулы имеют одинаковое относительное положение на рабочем листе. Как правило, формулы для вычисления итогов (например, промежуточные итоги) находятся под данными, к которым они относятся. Однако в некоторых случаях формулы для подсчета итогов располагаются над соответствующими диапазонами. Excel признает любой из этих методов, но вы должны быть последовательны в их использовании. Иначе, если используемые на листе формулы не будут согласованы, при автоматическом создании структуры вам не удастся получить желаемый результат.

#Структурирование

​Автоматическое создание структуры

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

📌 Если вы решите автоматически создать структуру на основе таблицы, созданной с помощью команды Вставка => Таблицы => Таблица, то программа не сможет это сделать. Создавать структуры на основе таблиц можно только вручную.

Чтобы автоматически создать структуру для некоторого диапазона данных, поместите табличный курсор в любую ячейку этого диапазона. Далее выберите команду Данные => Структура => Группировать => Создать структуру. Excel проанализирует формулы из выделенного диапазона и создаст структуру. В зависимости от формул будет создана либо горизонтальная, либо вертикальная структура, либо обе сразу. Если рабочий лист уже имеет структуру, будет задан вопрос, не хотите ли вы изменить ее. Щелкните на кнопке ОК, чтобы удалить старую структуру и создать новую.

#Структурирование

​​Создание структуры вручную

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

Чтобы создать группу строк, выполните следующие действия:
1. Выделите полностью все строки, которые нужно включить в группу. Единственный способ это сделать - щелкнуть на номере строки, а затем провести указателем мыши для того, чтобы выбрать соседние строки.
2. Выберите команду Данные => Структура => Группировать => Группировать. Excel отобразит символы для созданной группы.
3. Повторите эти действия для каждой создаваемой группы. При сжатии структуры все строки, составляющие группу, становятся скрытыми, однако итоговая строка, не включенная в группу, скрыта не будет и останется видимой.

Чтобы создать многоуровневую структуру, можно объединить несколько групп в одну. При создании многоуровневой структуры всегда следует начинать с самого нижнего уровня иерархии. Если случайно были сгруппированы неправильные столбцы или строки, их можно разгруппировать с помощью команды Данные => Структура => Разгруппировать => Разгруппировать.

Чтобы ускорить процесс группирования и разгруппирования, можно воспользоваться такими комбинациями клавиш:
<Alt+Shift+стрелка вправо> - группирование выбранных строк или столбцов;
<Alt+Shift+стрелка влево> - разгруппирование выбранных строк или столбцов.

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

#Структурирование

​​Добавление данных к структуре

Иногда возникает необходимость добавить к структуре дополнительные строки или столбцы. В некоторых случаях можно, не затрагивая структуру, вставить новые строки или столбцы так, чтобы они стали ее составной частью. Однако в определенных ситуациях может обнаружиться, что новая строка (или столбец) не стала частью структуры. Если структура была создана автоматически, еще раз выберите команду Данные => Структура => Группировать => Создать структуру. Excel попросит подтвердить, что вы хотите изменить существующую структуру.

Если же структура была создана вручную, то и изменения в нее нужно вносить вручную.

#Структурирование

​​Сокрытие символов структуры

Символы структуры занимают достаточно много места на экране (насколько много, зависит от количества уровней). Если же вам нужно максимально использовать пространство экрана, временно скройте эти символы, не удаляя структуру.
Для этого воспользуйтесь комбинацией клавиш <Ctrl+8>, которая, работая, как переключатель, отображает или скрывает символы структуры. Но учтите: если символы структуры скрыты, вы не сможете вручную расширять или сворачивать уровни структуры.

📌 Хотя символы структуры могут быть скрыты, сама структура при этом сохраняется и в рабочем листе отображаются данные ее текущего уровня, поэтому некоторые строки или столбцы могут быть скрыты.

#Структурирование