Как сделать связанные выпадающие списки в excel

Как сделать зависимые выпадающие списки в ячейках Excel

Зависимый выпадающий список позволяет сделать трюк, который очень часто хвалят пользователи шаблонов Excel. Трюк, который делает работу проще и быстрее. Трюк, благодаря которому ваши формы будут удобны и приятны.

Пример создания зависимого выпадающего списка в ячейке Excel

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

Каждый продавец сначала определял товарную группу, а затем конкретный товар из этой группы. Форма должна включать полное имя группы и определенный индекс товара. Поскольку набирать это вручную было бы слишком трудоемким (и раздражающим) занятием, я предложил очень быстрое и простое решение — 2 зависимых выпадающих списка.

Первым был список всех категорий продуктов, второй — список всех продуктов, находящихся в выбранной категории. Поэтому я создал выпадающий список, зависимый от выбора, сделанного в предыдущем списке (здесь вы найдете материал о том, как создать два зависимых раскрывающихся списка).

Тот же самый результат хочет получить пользователь шаблона домашнего бюджета где нужна категория и подкатегория расходов. Пример данных находится на рисунке ниже:

Так, например, если мы выберем категорию Развлечения, то в списке подкатегорий должно быть: Кинотеатр, Театр, Бассейн. Очень быстрое решение, если в своем домашнем бюджете вы хотите проанализировать более подробную информацию.

Список категорий и подкатегорий в зависимом выпадающем списке Excel

Признаюсь, что в предложенном мной варианте домашнего бюджета я ограничиваюсь только категорией, поскольку для меня такого разделения расходов вполне достаточно (название расходов / доходов рассматривается как подкатегория). Однако, если вам нужно разделить их на подкатегории, то метод, который я описываю ниже, будет идеальным. Смело используйте!

А конечный результат выглядит следующим образом:

Зависимый выпадающий список подкатегорий

Для того чтобы этого достичь, необходимо сделать немного другую таблицу данных, чем если бы мы создавали один раскрывающийся список. Таблица должна выглядеть так (диапазон G2:H15):

Рабочая исходная таблица Excel

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

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

Ну хорошо. Теперь, по очереди я опишу шаги создания зависимого выпадающего списка.

1. Имена диапазонов ячеек

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

Присвоим имена двум диапазонам. Список всех категорий и рабочий список категорий. Это будут диапазоны A3:A5 (список категорий в зеленой таблице на первом изображении) и G3:G15 (список повторяющихся категорий в фиолетовой рабочей таблице).

Для того чтобы назвать список категорий:

1. Выберите диапазон A3:A5.
2. В поле имени (поле слева от строки формулы) введите название «Категория».
3. Подтвердите с помощью клавиши Enter.

Такое же действие совершите для диапазона рабочего списка категорий G3:G15, который вы можете вызвать «Рабочий_Список». Этот диапазон мы будем использовать в формуле.

2. Создание раскрывающегося списка для категории

Это будет просто:

1. Выберите ячейку, в которую вы хотите поместить список. В моем случае это A12.
2. В меню «ДАННЫЕ» выберите инструмент «Проверка данных». Появится окно «Проверка вводимых значений».
3. В качестве типа данных выберите «Список».
4. В качестве источника введите: =Категория (рисунок ниже).
5. Подтвердите с помощью OK.

Проверка вводимых значений – Категория.

Раскрывающийся список для категории.

3. Создание зависимого выпадающего списка для подкатегории

Сейчас будет весело. Создавать списки мы умеем — только что это сделали для категории. Только единственный вопрос: «Как сказать Excelю выбрать только те значения, которые предназначены для конкретной категории?» Как вы, наверное, догадываетесь, я буду использовать здесь рабочую таблицу и, конечно же, формулы.

Начнем с того, что мы уже умеем, то есть с создания раскрывающегося списка в ячейке B12. Поэтому выберите эту ячейку и нажмите «Данные» / «Проверка данных», а в качестве типа данных — «Список».

В источник списка введите следующую формулу:

Вид окна «Проверка вводимых значений»:

Проверка вводимых значений для подкатегории в зависимом выпадающем списке

Как видите, весь трюк зависимого списка состоит в использовании функции СМЕЩ. Ну хорошо, почти весь. Помогают ей функции ПОИСКПОЗ и СЧЕТЕСЛИ. Функция СМЕЩ позволяет динамически определять диапазоны. Вначале мы определяем ячейку, от которой должен начинаться сдвиг диапазона, а в последующих аргументах определяем его размеры.

В нашем примере диапазон будет перемещаться по столбцу Подкатегория в рабочей таблице (G2:H15). Перемещение начнем от ячейки H2, которая также является первым аргументом нашей функции. В формуле ячейку H2 записали как абсолютную ссылку, потому что предполагаю, что мы будем использовать раскрывающийся список во многих ячейках.

Поскольку рабочая таблица отсортирована по Категории, то диапазон, который должен быть источником для раскрывающегося списка, будет начинаться там, где впервые встречается выбранная категория. Например, для категории Питание мы хотим отобразить диапазон H6:H11, для Транспорта — диапазон H12: H15 и т. д. Обратите внимание, что все время мы перемещаемся по столбцу H, а единственное, что изменяется, это начало диапазона и его высота (то есть количество элементов в списке).

Начало диапазона будет перемещено относительно ячейки H2 на такое количество ячеек вниз (по числу), сколько составляет номер позиции первой встречающейся категории в столбце Категория. Проще будет понять на примере: диапазон для категории Питание перемещен на 4 ячейки вниз относительно ячейки H2 (начинается с 4 ячейки от H2). В 4-ой ячейке столбца Подкатегория (не включая заголовок, так как речь идет о диапазоне с именем Рабочий_Список), есть слово Питание (его первое появление). Мы используем этот факт собственно для определения начала диапазона. Послужит нам для этого функция ПОИСКПОЗ (введенная в качестве второго аргумента функции СМЕЩ):

Высоту диапазона определяет функция СЧЕТЕСЛИ. Она считает все встречающиеся повторения в категории, то есть слово Питание. Сколько раз встречается это слово, сколько и будет позиций в нашем диапазоне. Количество позиций в диапазоне — это его высота. Вот функция:

Конечно же, обе функции уже включены в функцию СМЕЩ, которая описана выше. Кроме того, обратите внимание, что как в функции ПОИСКПОЗ, так и в СЧЕТЕСЛИ, есть ссылка на диапазон названный Рабочий_Список. Как я уже упоминал ранее, не обязательно использовать имена диапазонов, можно просто ввести $H3: $H15. Однако использование имен диапазонов в формуле делает ее проще и легко читаемой.

Одна формула, ну не такая уж и простая, но облегчающая работу и защищает от ошибок при вводе данных!

Два варианта использования этого трюка я уже представил. Интересно, как вы его будете использовать?

Выпадающий связанный список в excel

​Смотрите также​​ это в акт​ представления (цветов, шрифтов​​ показывать в выпадающем​ любое, но обязательно​​ функция​​ первичного выпадающего списка.​Toyota​​, создадим алфавит. Алфавит​ правилами Проверки данных,​ выпадающего списка (ячейки​ данных.​=ЕСЛИ(Таблица!C$7=Списки!$F$5;​СМЕЩ(Списки!$D$4;1;0;СЧЁТЕСЛИ(Списки!$B$5:$B$30;0));​Excel.​
​ добавлении строк или​​ всё. Можно подкорректировать​Выпадающие списки в Excel​​KolyvanOFF​
​ и т.д.)​ списке. По умолчанию​ без пробелов и​ПОИСКПОЗ (MATCH)​ Т.е. если в​. В Excel 2003​ сформируем с помощью​ то можно использовать​A1:A4​Если в поле Источник​СМЕЩ(Списки!$D$4;1;0;СЧЁТЕСЛИ(Списки!$B$5:$B$30;0));​СМЕЩ(Списки!$D$4;ПОИСКПОЗ(Таблица!A8;Списки!$C$5:$C$30;0);0;СЧЁТЕСЛИ(Списки!$C$5:$C$30;Таблица!A8)))​Сначала выберем из​ столбцов в таблицу,​
​ размер диапазона. Мы​бывают разные. Есть​: как вариант​При использовании этого способа,​ — 8, но​ начать с буквы!)​, которая, попросту говоря,​ нем есть текст​
​ и старше -​ формулы​
​ инструмент Выделение группы​
​на листе Список).​ указать через точку​СМЕЩ(Списки!$D$4;ПОИСКПОЗ(Таблица!B8;Списки!$C$5:$C$30;0);0;СЧЁТЕСЛИ(Списки!$C$5:$C$30;Таблица!B8)))​Эту формулу можно посмотреть​ выпадающего списка в​ все диапазоны в​ уменьшили размер диапазона​ простой​KolyvanOFF​ также возможно указывать​
​ можно больше, чего​ для выделенного диапазона​ выдает порядковый номер​ с пробелами, то​ это можно сделать​=СИМВОЛ(192+СТРОКА(Z1)-1)​ ячеек (Главная/ Найти​Для этого:​
​ с запятой единицы​Выражение ЕСЛИ(Таблица!С$7=Списки!$F$5; в данном случае​ через Диспетчер имен (имя​ ячейке Е1, чтобы​ таблице увеличиваются автоматически,​ «Юбка», чтобы в​раскрывающийся список Excel в​: _Boroda_, Классный список!​ в качестве​
​ не позволяет предыдущий​ (например​​ ячейки с выбранной​ придется их заменять​ в меню​где 192 –​ и выделить/ Выделение​выделяем​ измерения шт;кг;кв.м;куб.м, то​
​ выражение вернет значение​ формулы — Выбор),​ эта ячейка не​ не нужно их​ выпадающем списке не​ ячейке​_Boroda_​
​ListFillRange​ способ.​ ​Товары​
​ маркой (G7) в​ на подчеркивания с​Вставка — Имя -​ код русской буквы​ группы ячеек). Опция​А1:А4​ выбор будет ограничен​ ЛОЖЬ;​ но так как​ была пустой.​ настраивать.​ было пустой строки.​. Есть​:​
​не только одномерные​После нажатия на​). Нажмите​ заданном диапазоне (столбце​​ помощью функции​ Присвоить (Insert -​ А.​ Проверка данных этого​,​ этими четырьмя значениями.​Так как предыдущее выражение​ формула использует относительную​
​Затем, как в​​Остается просто вносить​Теперь устанавливаем​​многоуровневые зависимые выпадающие списки​
​KolyvanOFF​ диапазоны. Можно, например​ОК​ОК​ А)​ПОДСТАВИТЬ (SUBSTITUTE)​ Name — Define).​С помощью формулы массива​ инструмента позволяет выделить​нажимаем Формулы/ Определенные имена/​Теперь смотрим, что получилось.​
​ =ЛОЖЬ, то работает​ адресацию в ссылках​ первом случае, ставим​ нужные данные в​
​первый выпадающий список в​​ в Excel​, Вы ответы-то в​​ задать диапазон из​​списком можно пользоваться.​.​
​сдвиг_вправо = 1, т.к.​
​, т.е. формула будет​В Excel 2007​=ЕСЛИОШИБКА(ИНДЕКС($C$4:$C$35;​ ячейки, для которых​ Присвоить имя​ Выделим ячейку​ второе условие функции ЕСЛИ(),​ на ячейки Таблица!B$7 и Таблица!A8,​ в пустой ячейке​ таблицу и все.​ ячейки столбца А​
​. Это, когда, в​ теме читали?​ двух столбцов и​Чтобы вместо порядкового номера​Выделите ячейки (можно сразу​ мы хотим сослаться​ выглядеть как =ДВССЫЛ(ПОДСТАВИТЬ(F3;»​ и новее -​НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ(Фамилии;$C$4:$C$35&»*»)>0;​ проводится проверка допустимости​
​в поле Имя вводим​B1​ т.е. выражение СМЕЩ(Списки!$D$4;ПОИСКПОЗ(Таблица!B8;Списки!$C$5:$C$30;0);0;СЧЁТЕСЛИ(Списки!$C$5:$C$30;Таблица!B8))​ то для ее​ (не в таблице)​ Остальное сделает сама​
​.​ зависимости от выбранных​Это как раз​ нескольких строк, указав​ элемента выводилось его​ несколько), в которых​ на модели в​ «;»_»))​ на вкладке​СТРОКА($C$4:$C$35);»»);СТРОКА(Z1))-СТРОКА($C$3));»»)​ данных (заданная с​ Список_элементов, в поле​. При выделении ячейки​функция СМЕЩ() выбирает из столбца D​
​ просмотра нужно предварительно​ знак «равно». Выделяем​ таблица — изменит​У нас, в​ данных в первом​
​ и есть тот​ дополнительно, что выводить​ название можно дополнительно​ хотите получить выпадающий​ соседнем столбце (В)​Надо руками создавать много​Формулы (Formulas)​выведем в отдельный столбец​
​ помощью команды Данные/​ Область выбираем Книга;​ справа от ячейки​ (Потомки) все значения,​
​ выделить ячейку​ данные столбца А​ диапазоны и внесет​ примере, мы выделяем​ столбце выпадающего списка,​ самый способ, на​ нужно два столбца​

Как сделать связанные выпадающие списки в Excel легко.

​ использовать функцию​ список и выберите​​размер_диапазона_в_строках — вычисляем с​ ​ именованных диапазонов (если​с помощью​​ только те буквы​ Работа с данными/​Теперь на листе Пример,​ появляется квадратная кнопка​ у которых Родитель​
​В8​ (без названия столбца).​ новые данные в​ диапазон A2:A3. И,​ меняется выпадающий список​ который я ругаюсь.​ (свойство​ИНДЕКС (INDEX)​ в меню (на​
​ помощью функции​ у нас много​Диспетчера имен (Name Manager)​ алфавита, с которых​ Проверка данных). При​ выделим диапазон ячеек,​ со стрелкой для​ = Америка. ​
​на листе Таблица.​ У нас -​ соответствующие выпадающие списки.​
​ через «Проверки данных»​ в ячейках второго​ Что мне там​ColumnCount​, которая умеет выводить​ вкладке)​СЧЕТЕСЛИ (COUNTIF)​ марок автомобилей).​. Затем повторим то​ начинаются фамилии в​​ выборе переключателя Всех​ которые будут содержать​ выбора элементов из​
​Для этого выражение ПОИСКПОЗ(Таблица!B8;Списки!$C$5:$C$30;0) возвращает номер​ О том как​
​ это диапазон А2:А4.​У нас есть​ на закладке «Данные»,​​ столбца, третьего, т.д.​ не нравится:​=2). Тогда можно получить​ содержимое нужной по​Данные — Проверка (Data​, которая умеет подсчитать​Этот способ требует наличия​ же самое со​ перечне.​
​ будут выделены все​ Выпадающий список.​ выпадающего списка.​ позиции первой ячейки​ работает эта формула​Копируем формулу выделенного​ такие данные на​ устанавливаем выпадающие списки.​Здесь разберём​1. Куча имен.​ весьма привлекательные результаты,​ счету ячейки из​ — Validation)​
​ количество встретившихся в​ отсортированного списка соответствий​ списками Форд и​С помощью Проверки данных​ такие ячейки. При​вызываем Проверку данных;​Недостатки​
​ в столбце Родитель,​ см. в разделе​
​ диапазона. Выходим из​ листе.​ Тип данных –​двухуровневый зависимый выпадающий список​2. При изменении​ окупающие все потраченные​​ диапазона:​. Из выпадающего списка​ списке (столбце А)​ марка-модель вот такого​
​ Ниссан, задав соответственно​ создадим в ячейке​ выборе опции Этих​в поле Источник вводим​этого подхода: элементы​ содержащей значение Америка.​ статьи ниже. ​ ячейки клавишей «Esc».​Сделаем из этих данных​ выбираем «Список». А​ в Excel​ данных в шапке​ на дополнительные настройки​Этот способ частично напоминает​Тип данных (Allow)​ нужных нам значений​ вида:​ имена диапазонам​С1​
​ же выделяются только​
​ ссылку на созданное​
​ списка легко потерять​ Это позиция номер​ПРИМЕЧАНИЕ​ Выделяем ячейки, в​ форматированную таблицу Excel.​ в строке «Источник»​.​ все рушится.​ усилия:​ предыдущий. Основное отличие​выберите вариант​ ​ — марок авто​Для создания первичного выпадающего​ ​Ford​
​выпадающий список, содержащий​ те ячейки, для​ имя: =Список_элементов.​ (например, удалив строку​ 4. Именно с​
​: Функция ЕСЛИ() нужна для формирования​ которых будем создавать​ Делаем активной любую​ указываем имя диапазона.​Например, в первом​3. Имя должно​Способ 1.​ в том, что​Список (List)​
​ (G7)​ списка можно марок​и​ отобранные на предыдущем​ которых установлены те​Примечание​ или столбец, содержащие​ этой позиции, но​ списка самого верхнего​ выпадающие списки второго​ ячейку этих данных.​ Например, «=Наименование_товара».​ столбце из выпадающего​ начинаться с буквы​Примитивный​
​ на лист добавляется​и введите в​размер_диапазона_в_столбцах = 1, т.к.​ можно воспользоваться обычным​Nissan​ шаге буквы алфавита:​ же правила проверки​Если предполагается, что​ ячейку​ из другого столбца,​ уровня иерархии -​
​ уровня. У нас​ Заходим на закладку​Подробнее, как установить​ списка выбрали «Пальто».​ или подчеркивания, не​Способ 2.​ не элемент управления,​ строчку​ нам нужен один​ способом, описанным выше,​.​=СМЕЩ($D$4;;;СЧЁТЕСЛИ($D$4:$D$32;»*?»))​ данных, что и​ перечень элементов будет​
​B1​
​ будет формироваться наш выпадающий​
​ у него нет​ – это диапазон​ «Главная», в разделе​ выпадающий список, смотрите​ Во втором столбце​ содержать пробел и​Стандартный​
​ а элемент ActiveX​Источник (Source)​ столбец с моделями​ т.е.​При задании имен помните​
​Ячейке​ для активной ячейки.​ дополняться, то можно​); не удобно вводить​ список;​ Родителя.​ F1:F4 (окрашен зеленым​ «Стили» нажимаем кнопку​

Многоуровневый (3+) связанный список (типа предок-родитель) в MS EXCEL

​ в статье «Выпадающий​ появился выпадающий список​ прочие «недопустимые» знаки.​Способ 3.​»Поле со списком»​знак равенства и​В итоге должно получиться​дать имя диапазону D1:D3​

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

• ​Для ввода формулы выделите​
• ​ цветом). Через функцию​ «Форматировать как таблицу»​

​ список в Excel».​ размеров этого пальто.​shuker​Элемент управления​из раскрывающегося набора​ имя диапазона (т.е.​ что-то вроде этого:​ (например​

​ имена диапазонов в​присвоим имя Буква.​:​ большего размера, например,​

Решение

​ Подход годится для​ его длину. Для​ ячейку​

​ «Проверка данных» устанавливаем​
​ и выбираем первый​
​Устанавливаем​

​ А, если в​: Видимо я совсем​Способ 4.​ под кнопкой​=Товары​Осталось добавить выпадающий список​Марки​ Excel не должны​Для создания выпадающего списка,​Если выпадающий список​​А1:А10​​ маленьких (3-5 значений)​ этого используем выражение СЧЁТЕСЛИ(Списки!$C$5:$C$30;Таблица!В8).​B8​ выпадающий список. В​ шаблон таблицы.​

​зависимые выпадающие списки в​​ первом столбце этой​ не знаю как​Элемент ActiveX​Вставить (Insert)​).​

​ на основе созданной​) с помощью​​ содержать пробелов, знаков​​ содержащего перечень фамилий,​ содержит более 25-30​

​ неизменных списков.​​Чтобы добавить новое значение,​​, вызовите Диспетчер имен, и​ строку «Источник» диалогового​Обязательно ставим галочку у​ столбце В​ же ячейки из​ делать( Первый список​Сложность​

​с вкладки​Нажмите​ формулы к ячейке​Диспетчера имен (Name Manager)​ препинания и начинаться​ начинающихся на выбранную​

​ значений, то работать​ случае Выпадающий список​Преимущество​ например, новую Страну​

​ введите формулу:​ окна вставляем скопированную​

​ строки «Таблица с​.​ выпадающего списка выбрали​

• ​ получился, второй не​низкая​Разработчик (Developer)​ОК​ G8. Для этого:​с вкладки​
• ​ обязательно с буквы.​ букву, создадим Именованный​ с ним становится​
• ​ может содержать пустые​: быстрота создания списка.​ с городами и​Нажмите ОК. ​ формулу.​ заголовками».​Это второй уровень​ «Брюки», то во​ пойму по какому​средняя​:​.​выделяем ячейку G8​Формулы (Formulas)​ Поэтому если бы​ диапазон Вложенный_диапазон со​

Как работает эта формула?

​ неудобно. Выпадающий список​ строки.​​Элементы для выпадающего списка​​ улицами, нужно добавить​Опять выделите ячейку ​

• ​Но формулу снова​Нажимаем «ОК». Получилась такая​ выпадающих списков.​ втором столбце будет​ принципу должен осуществлять​высокая​Механизм добавления тот же​Все! Наслаждайтесь!​
• ​выбираем на вкладке​или в старых​ в одной из​ следующей формулой:​
• ​ одновременно отображает только​Избавиться от пустых строк​ можно разместить в​ в таблицу на​B8​ дорабатываем. Ставим две​

​ таблица.​Внимание!​​ выпадающий список с​​ поиск. Приложил исходный​Возможность настройки шрифта, цвета​

​ — выбираем объект​Важный нюанс. В качестве​Данные (Data)​ версиях Excel -​

​ марок автомобилей присутствовал​
​=СМЕЩ($A$1;ПОИСКПОЗ(Буква&»*»; Фамилии;0);;​
​ 8 элементов, а​

• ​ и учесть новые​ диапазоне на листе​ листе Списки нужное​
• ​, сформируйте выпадающий список​ круглые скобки, четыре​Сама эта таблица уже​Перед тем, как​
• ​ размерами брюк.​ файл, который хочу​ и т.д.​ из списка и​
• ​ источника данных для​команду​ через меню​ бы пробел (например​СЧЁТЕСЛИ(Фамилии;Буква&»*»))​ чтобы увидеть остальные,​ элементы перечня позволяет​ EXCEL, а затем​ количество строк. Чтобы​ на основе Проверки​
• ​ кавычки, два знака​ имеет имя. Смотрим​ устанавливать выпадающие списки​

​Итак, сделаем две​ сделать для облегчения​нет​ рисуем его на​ списка может выступать​Проверка данных (Data validation)​Вставка — Имя -​ Ssang Yong), то​Функция СЧЁТЕСЛИ() подсчитывает фамилии,​ нужно пользоваться полосой​ Динамический диапазон. Для​

​ в поле Источник​ не исправлять формулу,​ данных. В поле​ «&» и, вместо​ в Диспетчере имен.​​ в столбце В,​​ таблицы. Саму таблицу​ работы. На листе​нет​​ листе. А вот​​ и динамический именованный​или в меню​ Присвоить (Insert -​ его пришлось бы​ начинающиеся с выбранной​ прокрутки, что не​

​ этого при создании​ инструмента Проверки данных​ строки следует добавлять,​ Тип данных укажите​ слова «пальто» (это​Название этой таблицы можно​ выберите в первой​ сделаем на странице​

Выпадающий список в MS EXCEL на основе Проверки данных

​ 1 пометил красным​да​ дальше начинаются серьезные​ диапазон, например прайс-лист.​Данные — Проверка (Data​ Name — Define)​ заменить в ячейке​ буквы. Результат соответствует​ всегда удобно.​ Имени Список_элементов в​ указать ссылку на​ вставляя их между​ Список, в поле​ имя выделенного диапазона),​ поменять. Нажимаем на​ верхней ячейке столбца​ книги «Таблица». А​

​ где хочется видеть​Количество отображаемых строк​ отличия от предыдущего​

​ Тогда при дописывании​ — Validation)​выбрать на вкладке​ и в имени​ высоте диапазона, возвращаемой​В EXCEL не предусмотрена​

​ поле Диапазон необходимо​ этот диапазон.​ уже существующими строками. ​ Источник введите формулу​ ставим адрес верхней​ таблицу, заходим на​ А любое значение.​

​ списки сделаем на​ сп2. Если не​

А. Простейший выпадающий список — ввод элементов списка непосредственно в поле Источник

​всегда 8​ способа.​ новых товаров к​из выпадающего списка выбираем​Данные (Data)​ диапазона на нижнее​

​ функцией СМЕЩ(). Функция​​ регулировка размера шрифта​​ записать формулу =СМЕЩ(Список!$A$1;;;СЧЁТЗ(Список!$A:$A))​Предположим, что элементы списка​Теперь представим ситуацию, что​ =Выбор.​​ ячейке столбца с​​ закладку «Конструктор» и​ Главное, чтобы эта​

​ странице «Размеры». У​ сложно прошу помочь​любое​Во-первых, созданный выпадающий ActiveX​ прайсу, они будут​ вариант проверки​

​команду​ подчеркивание (т.е. Ssang_Yong).​​ ПОИСКПОЗ(), принимающая в​​ Выпадающего списка. При​Использование функции СЧЁТЗ() предполагает,​ шт;кг;кв.м;куб.м введены в​ после заполнения таблицы​С помощью Маркера заполнения​ первым уровнем выпадающих​

​ в разделе «Свойства»​​ ячейка не была​ нас есть такая​ в этом.​Быстрый поиск элемента по​ список может находится​​ автоматически добавляться к​​Список (List)​Проверка данных (Data validation)​Теперь создадим первый выпадающий​ качестве аргумента [искомое_значение]​ большом количестве элементов​
​ что заполнение диапазона​​ ячейки диапазона​

Б. Ввод элементов списка в диапазон (на том же листе, что и выпадающий список)

​ с помощью выпадающих​ скопируйте ячейку вправо​ списков.​ пишем свое название​ пустой. У нас​ таблица.​KolyvanOFF​ первым буквам​

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

​ имеет смысл сортировать​​ ячеек (​A1:A4​ списков, было изменено​ и вниз (или​Кстати, если вы​
​ таблицы. Мы написали​​ – это ячейка​И мы сделали такие​: _Boroda_, согласен. Просто​нет​ разных состояниях -​ одним часто используемым​ качестве​ вариант проверки​​ марки автомобиля. Выделите​​ позицию первой встретившейся​ список элементов и​A:A​, тогда поле Источник​ значение ячейки​ вверх и вправо): выпадающие​ выделили диапазон снизу​ имя таблицы –​ А2.​

​ списки.​ такой список как​нет​ режиме отладки, когда​ трюком для таких​Источника (Source)​Список (List)​ пустую ячейку и​ фамилии, начинающуюся с​ использовать дополнительную классификацию​), который содержит элементы,​

​ будет содержать =лист1!$A$1:$A$4​С10​ списки всех уровней будут​ вверх, то ставите​ «Товар».​

B. Ввод элементов списка в диапазон (на любом листе)

​Выделяем диапазон в​Внимание!​ у тебя вижу​да​ можно настраивать его​ списков является создание​

​знак равно и​и указать в​ откройте меню​ выбранной буквы. Результат​

​ элементов (т.е. один​ ведется без пропусков​Преимущество​. Например, вместо США​ сформированы автоматически. ​

​ адрес ячейки нижней.​В этой таблице уже​ столбце В (у​В списках названия​ впервые. Теперь буду​Необходимость использования дополнительной функции​ параметры и свойства,​ связанных выпадающих списков​

​ имя нашего диапазона,​
​ качестве​Данные — Проверка (Data​ соответствует смещению левой​​ выпадающий список разбить​​ строк (см. файл​
​: наглядность перечня элементов​

• ​ выберем Мексика. Естественно,​​Наполнять выпадающие списки будем​​ Вообщем, с какой​
• ​ все столбцы имеют​ нас – это​
• ​ столбцов (В, С,​ применять! Спасибо!​ИНДЕКС​

​ двигать его по​ (когда содержимое одного​ т.е.​Источника (Source)​

• ​ — Validation)​
• ​ верхней ячейки диапазона​ на 2 и​ примера, лист Динамический​

​ и простота его​
​ значение в ячейке​ из таблицы на​ ячейки выделили диапазон,​ динамические диапазоны. Это​ В2:В3). Снова через​​ D) должны полностью​​_Boroda_​нет​ листу и менять​ списка меняется в​

​=Модели​=Марки​или нажмите кнопку​ относительно ячейки​ более).​ диапазон).​ модификации. Подход годится​D10​

​ листе Списки (см.​ ту ячейку и​ значит, если мы​​ функцию «Проверка данных»​​ совпадать с названием​: Смотрите​да​ размеры и -​ зависимости от выбора​

​Вуаля!​

​или просто выделить​Проверка данных (Data Validation)​A1​Например, чтобы эффективно работать​Используем функцию ДВССЫЛ()​ для редко изменяющихся​не изменится (в​ файл примера):​ указываете (первую выделенную​ добавим в столбце​ выбираем «Тип данных»​

​ в первом столбце​​shuker​нет​ режиме ввода, когда​ в другом).​4 способа создать выпадающий​ ячейки D1:D3 (если​

​на вкладке​.​ со списком сотрудников​

​Альтернативным способом ссылки на​ списков.​ ней останется один​Для наглядности различные уровни​ в диапазоне). Мы​ ячейку, то диапазон​

• ​ – список. А​ (у нас –​
• ​,​Возможность создания связанных выпадающих​ единственное, что можно​Этот способ представляет собой​
• ​ список в ячейках​ они на том​Данные (Data)​
• ​В Диспетчере имен должны​ насчитывающем более 300​ перечень элементов, расположенных​Недостатки​

​ из городов США).​ выделены Условным форматированием.​ выделили диапазон сверху​ увеличится, и это​ в строке «Источник»​ это наименование товара​shuker​ списков​ — выбирать из​ вставку на лист​ листа​ же листе, где​если у вас​ отображать 3 имени:​

​ сотрудников, то его​ на другом листе,​: если добавляются новые​ Чтобы подсказать пользователю​Заполнение таблицы на листе Списки​

​ вниз, п.э. поставим​
​ слово сразу автоматически​ пишем такую формулу​ – ячейки А2:А4​: Посмотрел, спасибо, список​нет​ него данные. Переключение​ нового объекта -​Автоматическое создание выпадающих списков​ список).​ Excel 2007 или​Наконец, с помощью Проверки​ следует сначала отсортировать​ является использование функции​ элементы, то приходится​ об ошибке, используем​ нужно производить следующим​ в формуле адрес​ попадет в выпадающий​ =ДВССЫЛ(А2)​ должны совпадать с​ появился, но не​да​ между этими режимами​ элемента управления «поле​ при помощи инструментов​А вот для зависимого​

​ новее. Затем из​​ данных создадим в​
​ в алфавитном порядке.​ ДВССЫЛ(). На листе​ вручную изменять ссылку​ Условное форматирование.​ образом:​ ячейки Е1. Формула​ список. То же​Этой формулой мы говорим​ ячейками В1:D1).​ в том месте,​нет​

​ происходит с помощью​ со списком» с​ надстройки PLEX​ списка моделей придется​ выпадающего списка​ ячейке​ Затем создать выпадающий​ Пример, выделяем диапазон​ на диапазон. Правда,​Формула в правиле Условного​Столбец Номер уровня содержит​

​ получилась такая.​ самое и со​ Excel, что список​Если наименований много,​ моя вина, чт​shuker​ кнопки​ последующей привязкой его​Выбор фото из выпадающего​ создать именованный диапазон​Тип данных (Allow)​D1​ список, содержащий буквы​ ячеек, которые будут​ в качестве источника​ форматирования ищет в​ числовые коды уровней.​=ДВССЫЛ(«Товар[«&$E1&»]»)​

Вложенный связанный список в MS EXCEL

​ столбцом. Пример посмотрим​ нужно показывать, в​ то столбец можно​ оне указал точное​: Добрый день!​Режим Конструктора (Design Mode)​ к диапазонам на​

​ списка​ с функцией​выберите вариант​связанный список, содержащий​ алфавита. Второй выпадающий​ содержать выпадающий список,​ можно определить сразу​ таблице на листе​ Самый верхний уровень​Нажимаем «ОК». Получилось так.​ ниже.​ зависимости от значения​ транспонировать в строку.​ месторасположение. сейчас выделил​

​Помогите разобраться, создал​на вкладке​ листе. Для этого:​Выпадающий список с автоматическим​СМЕЩ​Список (List)​ фамилии, начинающиеся с​ список должен содержать​ вызываем Проверку данных,​
​ более широкий диапазон,​​ Списки пару Родитель-Потомок.​​ (0) содержит название​Можно выпадающие списки сделать​Итак, форматированную таблицу​

​ в ячейке столбца​ Как это сделать,​ красным цветом где​ связаные выпадающий список,​

​Разработчик (Developer)​В Excel 2007/2010 откройте​ удалением уже использованных​(OFFSET)​и в поле​​ буквы из ячейки​​ только те фамилии,​ в Источнике указываем​ например,​

​ Если такая пара​
​ Регионов. Можно создать​ на другом листе,​ сделали. Всё. Осталось​

​ смотрите в статье​
​ должен быть список​
​ но оказалась проблема,​

​:​ вкладку​ элементов​, который будет динамически​Источник (Source)​

​С1​ которые начинаются с​​ =ДВССЫЛ(«список!A1:A4»).​​A1:A100​ не найдена, то​ любое количество уровней;​

​ тогда в формулу​

​ сделать выпадающие списки.​​Здесь все просто.​​ «Как поменять местами​

​ 1. Помогите еще​ что нельзя в​Если эта кнопка нажата,​Разработчик (Developer)​Динамическая выборка данных для​ ссылаться только на​

​выделите ячейки с​
​. Формула проста: =Вложенный_диапазон.​

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

​В итоге, мы должны​ списком. Для решения​

​: при переименовании листа​ список может содержать​ заливкой.​​ из столбца Потомок​​ напишем название листа​ нужного диапазона, чтобы​ диапазона (столбца) состоит​​ в Excel» тут.​​shuker​

​ вставлять пробелы или​ настраивать параметры выпадающего​ версиях — панель​​ ИНДЕКС и ПОИСКПОЗ​​ марки. Для этого:​​ ячейки в нашем​​ получить работающий выпадающий​ такой задачи может​ – формула перестает​​ пустые строки (если,​​При заполнении ячеек данными,​ более верхнего уровня;​​ и поставим восклицательный​​ потом не писать​ из нескольких слов.​Как настроить Excel,​

Связанные (зависимые) выпадающие списки

Способ 1. Функция ДВССЫЛ (INDIRECT)

​: Получилось. Спасибо. ​ тире. Например Орехово-Зуево​​ списка, нажав соседнюю​​ инструментов​У кого мало времени​Нажмите​ примере). После нажатия​ список в ячейке​ быть использована структура​ работать. Как это​ например, часть элементов​ часто необходимо ограничить​Столбец Потомок связывает значений​ знак.​ его вручную.​ Например, «Зимние пальто».​ чтобы при добавлении​Anton_98rus​ выбираем из главного​ кнопку​​Формы (Forms)​​ и нужно быстро​Ctrl+F3​ на​

​D1​ Связанный список или​ можно частично обойти​ была удалена или​

​ возможность ввода определенным​ из 2-х соседних​Как еще можно​Первый уровень связанных выпадающих​ А в имени​ ячеек в список​​: Ребят добрый день.​​ списка а районы​Свойства (Properties)​через меню​ ухватить суть -​​или воспользуйтесь кнопкой​ОК​. Выбрав в ячейке​​ Вложенный связанный список.​ см. в статье​ список только что​​ списком значений. Например,​​ уровней. Нужно иметь​​ применить готовые шаблоны​​ списков в Excel.​ диапазона нельзя ставить​ столбца A, автоматически​ Прошу у Вас​ его уже не​​, которая откроет окно​​Вид — Панели инструментов​​ смотрим обучающее видео:​​Диспетчер имен (Name manager)​

​первый выпадающий список​С1​Если Ваш Выпадающий список​ Определяем имя листа.​ был создан). Чтобы​ имеется ячейка, куда​ ввиду, что если​ таблиц Excel, читайте​В любой пустой​ пробел. Имя диапазона​ писалось название нового​ помощи.​ отображаются.​ со списком всех​ — Формы (View​Кому интересны подробности и​на вкладке​

​ готов:​, например, букву «В»,​ стал очень длинным​Ввод элементов списка в​ пустые строки исчезли​​ пользователь должен внести​ мы начали заполнять,​​ в статье «Готовые​​ ячейке пишем «=»​​ напишем так «Зимние_пальто».​​ столбца, смотрите в​​Открыл мебельное производство​А потому все,​ возможных настроек для​ — Toolbars -​​ нюансы всех описанных​​Формулы (Formulas)​​Теперь создадим второй выпадающий​​ выпадающий список в​​ и с ним​​ диапазон ячеек, находящегося​ необходимо сохранить файл.​ название департамента, указав​ например, все страны​ таблицы Excel».​​ (знак «равно»), выделяем​​ Но формула ДВССЫЛ​ статье «Как добавить​

​ и делаю таблицу​ что Вы Правила​ выделенного объекта:​ Forms)​ способов — дальше​. В версиях до​ список, в котором​​ ячейке​​ стало не удобно​​ в другой книге​​Второй недостаток: диапазон источника​ где он работает.​

​ Америки, то нужно​

​Как сделать связанный​

​ ячейки шапки таблицы​ не найдет этот​ столбец в Excel​ расчета стоимости мебели,​

​ форума не читаете​​Самые нужные и полезные​​. Если этой вкладки​ по тексту.​ 2003 это была​ будут отображаться модели​

​D1​​ работать, то создание​

• ​Если необходимо перенести диапазон​ должен располагаться на​ Логично, предварительно создать​ полностью закончить этот​​ выпадающий список, фамилии​​ – это будет​ диапазон. Тогда формулу​ автоматически».​ и дабы не​ и пример не​ свойства, которые можно​ не видно, то​Один щелчок правой кнопкой​ команда меню​ выбранной в первом​
• ​будет содержать фамилии​ вложенного связанного списка​ с элементами выпадающего​ том же листе,​ список департаментов организации​ список, затем переходить​ в котором можно​ первый выпадающий список.​ нужно написать так.​​Как сделать в Excel​​ округлять стоимость ЛДСП​ кладете.​ и нужно настроить:​
• ​ нажмите кнопку​ мыши по пустой​Вставка — Имя -​ списке марки. Также​

Способ 2. Список соответствий и функции СМЕЩ (OFFSET) и ПОИСКПОЗ (MATCH)

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

​ и позволить пользователю​ к следующему региону.​ выбирать по алфавиту,​Копируем это адрес. Нажимаем​ =ДВССЫЛ(ПОДСТАВИТЬ(A2;» «;»_»))​

• ​ динамический диапазон​ для всех и​​_Boroda_​​ListFillRange​​Офис — Параметры Excel​​ ячейке под столбцом​​ Присвоить (Insert -​​ как в предыдущем​А​Часто при вводе данных​​ книгу (например, в​ список, т.к. для​ лишь выбирать значения​
• ​ Выполнение этого требования​​ смотрите в статье​​ клавишу «Esc» -​​Если список на​
• ​- чтобы размер​ тем самым не​​: Как я уже​​- диапазон ячеек,​-​​ с данными, команда​​ Name — Define)​​ случае, откройте окно​, но только те, которые​ в таблицу пользуются​ книгу Источник.xlsx), то​ правил Проверки данных нельзя​

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

• ​ контекстного меню​​Создайте новый именованный диапазон​​Проверки данных​​ начинаются с буквы​​ Выпадающим (раскрывающимся) списком.​​ нужно сделать следующее:​​ использовать ссылки на​ Этот подход поможет​ работы вышеуказанной формулы.​​ в Excel по​ ячейки.​ в формуле указываем​
• ​ добавлении или убавлении​ с заказчиков хочу​ нужен.​​ для списка. Выделить​​Отображать вкладку Разработчик на​​Выбрать из раскрывающегося списка​​ с любым именем​, но в поле​ «В».​

​ Предположим, что в​

​в книге Источник.xlsx создайте​

​ другие листы или​ ускорить процесс ввода​ Выпадающие списки формируются​ алфавиту».​Теперь выделяем ячейки,​ название этого листа.​ ячеек менялся автоматически,​

​ сделать табличку с​​А без прмера​​ мышью диапазон он​ ленте (Office Button​(Choose from drop-down list)​ (например​Источник​Этот фокус основан на​ таблицу необходимо вводить​ необходимый перечень элементов;​ книги (это справедливо​

​ и уменьшить количество​ именно из этого​

​Для сложных иерархических структур​

• ​ в которые будем​ Напишем так. =ДВССЫЛ(»Размеры!А2:А4»)​ смотрите в статье​
• ​ выпадающими взаимосвязаными списками.​ могу сказать только​​ не даст, надо​​ — Excel Options​или нажать сочетание​Модели​нужно будет ввести​ применении функции​ фамилии, выбирая их​
• ​в книге Источник.xlsx диапазону​ для EXCEL 2007​ опечаток.​ столбца.​
• ​ с тремя и​ устанавливать выпадающий список.​​Нажимаем «ОК». Теперь​​ «Чтобы размер таблицы​Сама суть вопроса:​ одно — Вы​ просто вписать его​ — Show Developer​ клавиш​
• ​) и в поле​ вот такую формулу:​ДВССЫЛ (INDIRECT)​

​ из длинного списка.​ ячеек содержащему перечень​

​ и более ранних).​Выпадающий список можно создать​Рассмотрим как формируется выпадающий​ более уровнями создадим​

• ​ Мы выделили диапазон​
• ​ во втором столбце​​ Excel менялся автоматически».​​Мы имеем несколько​​ создаете кучу именованных​​ руками с клавиатуры​​ Tab in the​ALT+стрелка вниз​
• ​Ссылка (Reference)​=ДВССЫЛ(F3)​​, которая умеет делать​​ Если длина списка​ элементов присвойте Имя,​​Избавимся сначала от второго​​ с помощью Проверки​ список в ячейке​ Многоуровневый связанный список​​ Е1:Е4 (окрашен в​

​ установлены выпадающие списки,​

Ссылки по теме

• ​Теперь нужно присвоить​ производителе(Еггер, Кроношпан,Невский ламинат)​ диапазонов (есть такой,​
• ​ (например, Лист2!A1:A5)​ Ribbon)​. Способ не работает,​
• ​в нижней части​или =INDIRECT(F3)​
• ​ одну простую вещь​ более 30 элементов,​ например СписокВнеш;​
• ​ недостатка – разместим​ данных ​B8 ​

Выпадающий список в ячейке листа

Видео

​ типа Предок-Родитель. Теперь​ желтый цвет). Вставляем​ которые меняются, в​ имена всем этим​

​ у каждого производителя​ на мой взгляд​LinkedCell​Найдите значок выпадающего списка​

Способ 1. Примитивный

​ если ячейку и​ окна введите руками​где F3 — адрес​ — преобразовывать содержимое​ то процесс поиска​​откройте книгу, в которой​ ​ перечень элементов выпадающего​​или с помощью элемента​на листе Таблица (заполняем​​ структуры типа: Регион-Страна-Город-Улица​​ выпадающий список как​ зависимости от того,​ спискам. У нас​ своя цветовая гамма,​ — крайне дурной,​- связанная ячейка,​ среди элементов управления​ столбец с данными​ следующую формулу:​

Способ 2. Стандартный

1. ​ ячейки с первым​ любой указанной ячейки​ может быть достаточно​ предполагается разместить ячейки​
2. ​ списка на другом​ управления формы Поле​ Регионы). ​ можно создавать в​​ обычно. Нажимаем «Проверка​ что написано в​​ в списках четыре​ и соответственно для​​ способ связывания вып.​ куда будет выводиться​ форм (не ActiveX!).​​ отделяет хотя бы​​=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)​​ выпадающим списком (замените​​ в адрес диапазона,​​ утомительным (несколько облегчает​​ с выпадающим списком;​ листе.​ со списком (см.​Выражение ЕСЛИ(Таблица!B$7=Списки!$F$5; проверяет заполняем​ MS EXCEL.​ данных», выбираем «Список».​​ ячейках первого столбца.​​ диапазона (четыре столбца).​​ каждой группы цветов​​ списков). А в​
3. ​ выбранный из списка​ Ориентируйтесь по всплывающим​ одна пустая строка​=OFFSET($A$1;MATCH($G$7;$A:$A;0)-1;1;COUNTIF($A:$A;$G$7);1)​ на свой).​ который понимает Excel.​​ ситуацию предварительная сортировка​выделите нужный диапазон ячеек,​​В правилах Проверки данных (также​​ статью Выпадающий (раскрывающийся)​​ ли мы столбец​​В статье Многоуровневый связанный список​​ В диалоговом окне​ Получилось так.​​ Легко и быстро​​ своя цена, перебрал​ названиях именованных диапазонов​​ элемент​​ подсказкам -​

​ или вам нужен​​Ссылки должны быть абсолютными​​Все. После нажатия на​

​ этого списка).​ вызовите инструмент Проверка​ как и Условного​ список на основе​ Регионы (самый верхний​ в MS EXCEL мы​ «Проверка вводимых значений»​Копируем формулу вниз по​ сделать так.​ в интернете кучу​ тире автоматически заменяется​ListRows​Поле со списком​ товар, который еще​ (со знаками $).​ОК​ в ячейке лежит​В этом случае имеет​

Способ 3. Элемент управления

​ данных, в поле​ форматирования) нельзя впрямую​ элемента управления формы).​ уровень с номером​ уже создавали многоуровневый​ в строку «Источник»​ столбцу. Мы создали​Как присвоить имя диапазону​

1. ​ формул и все​ на нижнее подчеркивание​​- количество отображаемых​​:​ ни разу не​ После нажатия Enter​​содержимое второго списка​​ текст «А1», то​​ смысл создать два​ Источник укажите =ДВССЫЛ(«[Источник.xlsx]лист1!СписокВнеш»);​ указать ссылку на​В этой статье создадим​​ 0) или нет.​ список. Но, у​ вставляем скопированную формулу​​ двухуровневый выпадающий список​​ в​​ троможу(сделать не могу)​​ (при автоматической создаче​ строк​Щелкните по значку​ вводился выше:​ к формуле будут​ будет выбираться по​
2. ​ функция выдаст в​ выпадающих списка, один​При работе с перечнем​ диапазоны другого листа​ Выпадающий список с​​ В данном случае​​ того списка было​

• ​ результате ссылку на​​ для букв алфавита,​ элементов, расположенным в​ (см. Файл примера):​ помощью Проверки данных​
• ​ выражение вернет значение​​ 2 недостатка:​ формулу нужно доработать.​Другой способ сделать​Выделяем диапазон ячеек​
• ​1. в ячейке​​shuker​- шрифт, размер,​ горизонтальный прямоугольник -​ которые должны попасть​ листов — не​ в первом списке.​ ячейку А1. Если​

​ с которых начинаются​​ другой книге, файл​​Пусть ячейки, которые должны​

​ (Данные/ Работа с​ ИСТИНА;​слишком сложные формулы;​ Допишем впереди –​​ связанный выпадающий список​​ всех списков сразу​ А1 создать вып.​: Прикрепляю пример.​ начертание (курсив, подчеркивание​

Способ 4. Элемент ActiveX

​ будущий список.​ в выпадающий список​ пугайтесь :)​Минусы​ в ячейке лежит​ фамилии, другой –​​ Источник.xlsx должен быть​​ содержать Выпадающий список,​ данными/ Проверка данных)​​Так как предыдущее выражение​​максимальное количество уровней -​​ ДВССЫЛ, поставим кавычки​​ в Excel, смотрите​

​ вместе с шапкой​ список(Еггер,Кроношпан,Невский ламинат)​Дело в том,​ и т.д. кроме​Щелкните по нарисованному списку​ (например, наименованиями товаров).​Функция​такого способа:​

​ слово «Маша», то​ Связанный список, для​ открыт и находиться​ размещены на листе​ с типом данных​ =ИСТИНА, то работает​ 3.​ и круглые скобки.​ в статье «Как​ таблицы списков –​это могем а​ что нижнее подчеркивание​ цвета)​ правой кнопкой мыши​Если у вас Excel​СМЕЩ (OFFSET)​В качестве вторичных (зависимых)​​ функция выдаст ссылку​​ перечня фамилий, начинающихся​​ в той же​​ Пример,​

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

​ 2003 или старше​умеет выдавать ссылку​ диапазонов не могут​

• ​ на именованный диапазон​​ на выбранную букву.​ папке, иначе необходимо​а диапазон с перечнем​Выпадающий список можно сформировать​ ЕСЛИ(), т.е. выражение СМЕЩ(Списки!$D$4;1;0;СЧЁТЕСЛИ(Списки!$B$5:$B$30;0))​ этих недостатков, но​=ДВССЫЛ(«Товар[#Заголовки]»)​ списки в Excel​
• ​ диапазон А1:D4. На​​2.В ячейке В1​ как это название​и​Формат объекта (Format control)​
• ​ — выберите в​​ на диапазон нужного​ выступать динамические диапазоны​
• ​ с именем​​Создадим в столбце​ указывать полный путь​ элементов разместим на​ по разному.​
• ​функция СМЕЩ() выбирает из​​ исходный список, содержащий​​Нажимаем «ОК».​​ легко».​ закладке «Формулы» в​

​ создать вып. список​ оно выделено жирным​BackColor​. В появившемся диалоговом​ меню​ размера, сдвинутый относительно​ задаваемые формулами типа​Маша​A​ к файлу. Вообще​ другом листе (на​Самым простым способом создания​ столбца D (Потомки)​

​ названия стран, городов​Так как в самой​Здесь мы рассмотрим​​ разделе «Определенные имена»​​ выбора цвета​ шрифтом.​- цвет текста​ окне задайте​Вставка — Имя -​ исходной ячейки на​СМЕЩ (OFFSET)​и т.д. Такой,​​отсортированный по алфавиту​​ ссылок на другие​ листе Список в​ Выпадающего списка является​ все значения, у​ и улиц получается​

Итоговая сравнительная таблица всех способов

​ таблице с данными​​ другой вариант,​	​ нажимаем функцию «Создать​​3. какую формулу​	​_Boroda_​​ и фона, соответственно​	​Формировать список по диапазону​​ Присвоить​
​ заданное количество строк​	​. Для первичного (независимого)​	​ своего рода, «перевод​	​ перечень фамилий, см.​
​ листы лучше избегать​ файле примера).​	​ ввод элементов списка​	​ которых уровень равен​	​ немного громоздким и​
​ диапазоны динамичные, то​	​как легко​	​ из выделенного фрагмента».​
​ вписать чтоб в​: Что значит «не​	​Большим и жирным плюсом​	​- выделите ячейки​	​(Insert — Name -​
​ и столбцов. В​​ списка их использовать​	​ стрелок» ;)​	​ файл примера.​	​ или использовать Личную​
​Для создания выпадающего списка,​ непосредственно в поле​	​ 0. Из этих​	​ его нужно наполнять​	​ если мы добавим​

Связанные (зависимые) выпадающие списки (Формулы/Formulas)

​сделать связанные выпадающие списки​​ В появившемся диалоговом​
​ ячейке С1 появлялась​ подходит»? Я ж​ этого способа является​ с наименованиями товаров,​ Define),​ более понятном варианте​ можно, а вот​Возьмем, например, вот такой​Через меню Формулы/ Определенные​ книгу макросов Personal.xlsx​ элементы которого расположены​
​ Источник инструмента Проверка​ значений формируется выпадающий список.​ придерживаясь определенных правил.​ столбец, это слово​ в Excel​

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

​ цвета​​ заменяется». И еще​
​ к нужному элементу​ в список​ или новее -​ таков:​ быть определен жестко,​ Toyota, Ford и​

​ создадим Именованный диапазон​​Если нет желания присваивать​ можно использовать два​Предположим, в ячейке​ ячейке ​ получить вот такой​ таблицы и слово​
​ сделать раскрывающиеся списки,​ «В строке выше».​

​Файл прикрепил​​ я написал, что​ в списке при​Связь с ячейкой​

​ откройте вкладку​​=СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; размер_диапазона_в_строках;​

​ без формул. Однако,​​ Nissan:​

​ Фамилии =Лист1!$A$2:$A$153.​​ имя диапазону в​​ подхода. Один основан​​B1​С8 ​
​ 4-х уровневый связанный​ отобразится в выпадающем​ смотрите в статье​Нажимаем «ОК». Всё, имена​На форуме запрещено​ это дурной способ.​
​ вводе первых букв​
​- укажите ячейку​Формулы (Formulas)​ размер_диапазона_в_столбцах)​
​ это ограничение можно​Выделим весь список моделей​Для создания Выпадающего списка,​ файле Источник.xlsx, то​ на использовании Именованного​

​необходимо создать выпадающий​​на листе Таблица (заполняем​ список:​ списке. Например, добавим​ «Связанные выпадающие списки​ присвоены. На закладке​ задавать новые вопросы​Держите нормальным способом​ с клавиатуры(!), чего​ куда нужно выводить​и воспользуйтесь кнопкой​Таким образом:​ обойти, создав отсортированный​ Тойоты (с ячейки​ содержащего все буквы​ формулу нужно изменить​

​ диапазона, другой –​​ список для ввода​ Страны). ​Выпадающие списки будем формировать​ слово «плащ». Получилось​ в Excel».​

​ «Формулы» нажимаем функцию​​ в чужих темах.​​ сделанное​​ нет у всех​

​ порядковый номер выбранного​​Диспетчер имен (Name Manager)​начальная ячейка — берем​ список соответствий марка-модель​ А2 и вниз​ алфавита, с которых​ на =ДВССЫЛ(«[Источник.xlsx]лист1!$A$1:$A$4»)​ функции ДВССЫЛ().​ единиц измерений. Выделим​В этом случае формула​ с помощью одной​

​ так.​​Способ, который мы​

​ «Диспетчер имен».​​ Читаем Правила, создаём​shuker​ остальных способов. Приятным​
​ пользователем элемента.​, затем​ первую ячейку нашего​ (см. Способ 2).​ до конца списка)​ начинаются фамилии в​СОВЕТ:​Используем именованный диапазон​ ячейку​ будет несколько другая,​ большой Именованной формулы:​
​Второй уровень связанных выпадающих​
​ сейчас рассмотрим удобен​Здесь перечислены все наши​ свою тему. Эта​: Спасибо огромное! Буду​ моментом, также, является​Количество строк списка​Создать​ списка, т.е. А1​Имена вторичных диапазонов должны​ и дадим этому​
​ столбце​
​Если на листе​Создадим Именованный диапазон Список_элементов,​B1​
​ т.к. в ней​=ЕСЛИ(Таблица!B$7=Списки!$F$5;​
​ списков в​ тем, что при​ диапазоны списков. Проверили​
​ тема закрыта​ пробовать перенести все​ возможность настройки визуального​- сколько строк​. Введите имя (можно​
​сдвиг_вниз — нам считает​
​ совпадать с элементами​ диапазону имя​А​ много ячеек с​ содержащий перечень элементов​и вызовем Проверку​

Связанные (зависимые) выпадающие списки

Этот фокус основан на применении функции ДВССЫЛ (INDIRECT) , которая умеет делать одну простую вещь — преобразовывать содержимое любой указанной ячейки в адрес диапазона, который понимает Excel. То есть, если в ячейке лежит текст «А1», то функция выдаст в результате ссылку на ячейку А1. Если в ячейке лежит слово «Маша», то функция выдаст ссылку на именованный диапазон с именем Маша и т.д. Такой, своего рода, «перевод стрелок» 😉

Возьмем, например, вот такой список моделей автомобилей Toyota, Ford и Nissan:

Выделим весь список моделей Тойоты (с ячейки А2 и вниз до конца списка) и дадим этому диапазону имя Toyota на вкладке Формулы (Formulas) с помощью Диспетчера имен (Name Manager) . Затем повторим то же самое со списками моделей Ford и Nissan, задав соответственно имена диапазонам Ford и Nissan.

При задании имён помните о том, что имена диапазонов в Excel не должны содержать пробелов, знаков препинания и начинаться обязательно с буквы. Поэтому если бы в одной из марок автомобилей присутствовал бы пробел (например Ssang Yong), то его пришлось бы заменить в ячейке и в имени диапазона на нижнее подчеркивание (т.е. Ssang_Yong).

Теперь создадим первый выпадающий список для выбора марки автомобиля. Выделите пустую ячейку (на картинке выше — зелёную) и нажмите кнопку Проверка данных (Data Validation) на вкладке Данные (Data) . Затем из выпадающего списка Тип данных (Allow) выберите вариант Список (List) и в поле Источник (Source) выделите ячейки с названиями марок (желтые ячейки в нашем примере). После нажатия на ОК первый выпадающий список готов.

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

где F2 — адрес ячейки с первым выпадающим списком (замените на свой).

Все. После нажатия на ОК содержимое второго списка будет выбираться по имени диапазона, выбранного в первом списке.

Минусы такого способа:

• Надо руками создавать много именованных диапазонов (если у нас много марок автомобилей).
• В качестве вторичных (зависимых) диапазонов не могут выступать динамические диапазоны задаваемые формулами типа СМЕЩ (OFFSET) . Для первичного (независимого) списка их использовать можно, а вот вторичный список должен быть определен жестко, без формул. Однако, это ограничение можно обойти, создав справочник соответствий марка-модель (см. Способы 3 и 4).
• Имена вторичных диапазонов должны совпадать с элементами первичного выпадающего списка. Т.е. если в нем есть текст с пробелами, то придется их заменять на подчеркивания с помощью функции ПОДСТАВИТЬ (SUBSTITUTE) , т.е. формула будет выглядеть как:

Способ 2. Умные таблицы

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

Таким образом, мы:

1. Сначала преобразуем наши справочники в «умные» таблицы, используя сочетание клавиш Ctrl + T или команду Главная — Форматировать как таблицу (Home — Format as Table) и
2. Даём им имена (Фрукты, Овощи, Зелень) на вкладке Конструктор (Design) в поле Имя таблицы (Table Name) .
3. Создаём первый и второй (связанный) выпадающие списки точно так же, как в предыдущем способе с функцией ДВССЫЛ (INDIRECT).

Способ 3. Отсортированный справочник

Этот способ требует наличия отсортированного списка соответствий марка-модель вот такого вида:

Для создания первичного выпадающего списка можно марок можно воспользоваться обычным способом, описанным выше, т.е. выбрать на вкладке Данные (Data) команду Проверка данных (Data validation) и указать в качестве источника жёлтые ячейки с марками

А вот для зависимого списка моделей придется создать именованный диапазон с функцией СМЕЩ (OFFSET), который будет динамически ссылаться только на ячейки моделей определенной марки. Для этого:

• Нажмите сочетание клавиш Ctrl + F3 или воспользуйтесь кнопкой Диспетчер имен (Name manager) на вкладке Формулы (Formulas) .
• Создайте новый именованный диапазон с любым именем (например Модели) и в поле Ссылка (Reference) в нижней части окна введите руками следующую формулу:

Ссылки должны быть абсолютными (со знаками $). После нажатия Enter к формуле будут автоматически добавлены имена листов — не пугайтесь.

Работает это следующим образом. Функция СМЕЩ (OFFSET) умеет выдавать ссылку на диапазон нужного размера, сдвинутый относительно исходной ячейки на заданное количество строк и столбцов. В более понятном варианте синтаксис этой функции таков:

=СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; высота_диапазона_в_строках; ширина_диапазона_в_столбцах)

• начальная ячейка — берем первую ячейку нашего списка, т.е. А1
• сдвиг_вниз — нам считает функция ПОИСКПОЗ (MATCH), которая, попросту говоря, выдает порядковый номер ячейки с выбранной маркой (G7) в заданном диапазоне (столбце А)
• сдвиг_вправо = 1, т.к. мы хотим сослаться на модели в соседнем столбце (В)
• высота_диапазона_в_строках — вычисляем с помощью функции СЧЕТЕСЛИ (COUNTIF), которая умеет подсчитать количество встретившихся в списке (столбце А) нужных нам значений — марок авто (G7)
• ширина_диапазона_в_столбцах = 1, т.к. нам нужен один столбец с моделями

В итоге должно получиться что-то вроде этого:

Осталось добавить выпадающий список на основе созданной формулы к ячейке G8. Для этого:

• выделяем ячейку G8
• выбираем на вкладке Данные (Data) команду Проверка данных (Data validation)
• из выпадающего списка выбираем вариант проверки Список (List) и вводим в качестве Источника (Source) знак равно и имя нашего диапазона, т.е. =Модель

И наш связанный список моделей готов.

Способ 4. Неотсортированный справочник

Предыдущий способ неплох, но предполагает наличие обязательно отсортированного справочника вида «марка-модель», где названия марок повторяются многократно. Иногда же требуется в качестве справочника использовать более банальную конструкцию:

Первый уровень выпадающего списка (диапазон А2:А14 на рисунке выше) здесь делается классическим образом через команду Данные — Проверка данных — Список (Data — Validation — List) и в качестве источника указать зелёные ячейки с названиями категорий.

А для связанных выпадающих списков во втором столбце (B2:B14) в поле Источник (Source) мы используем хитрую формулу:

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

Связанные выпадающие списки в Excel ⁠ ⁠

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

1. С помощью функции ДВССЫЛ

ДВССЫЛ (INDIRECT) преобразовывает содержимое любой указанной ячейки в адрес диапазона, который понимает Excel. Т.е. если в ячейке лежит текст «А1», то функция выдаст в результате ссылку на ячейку А1. Если в ячейке лежит слово «Мир», то функция выдаст ссылку на именованный диапазон со словом Мир.

Итак, начнём. Выделяем весь список «Певец» (с ячейки А2 и вниз до конца списка) и нажав Ctrl+F3 или на вкладке Формулы — Диспетчер имён – Создать задаём имя диапазону (Певец). Затем таким же способом проделаем то же самое с остальными списками «Группа» и «Дата».

Слова в диапазонах не должны содержать пробелов, знаков препинания и должны начинаться обязательно с буквы. Поэтому пробелы необходимо заменять на нижнее подчёркивание.

Теперь создадим первый выпадающий список для выбора певца. Выделяем пустую ячейку и на вкладке «Данные» нажимаем кнопку «Проверка данных». Затем из выпадающего списка «Тип данных» выбираем вариант «Список» и в поле «Источник» выделяем ячейки с названиями (ячейки A1:C1).

После нажатия на ОК первый выпадающий список готов:

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

Теперь содержимое второго выпадающего списка будет выбираться по имени диапазона, выбранного в первом.

Минусы данного способа:

— Имена вторичных диапазонов должны совпадать с элементами первичного выпадающего списка. Т.е. если в нём есть текст с пробелами, то придётся их заменять на подчёркивания с помощью функции ПОДСТАВИТЬ, =ДВССЫЛ(ПОДСТАВИТЬ(E2;» «;»_»))

=INDIRECT(SUBSTITUTE(E2;» «;»_»))

— Надо вручную создавать много именованных диапазонов (если у нас много значений).

2. Для следующего способа нам нужна таблица, разбитая по категориям и видам:

Как и в предыдущем способе через Ctrl+F3 создаём именованные диапазоны каждой категории (без заголовков). Т.е. сначала «Мебель», затем «Техника» и так каждую по отдельности.

Теперь создаём первый выпадающий список по категориям. Для этого на вкладке «Данные» — «Проверка данных» — «Тип данных» выбираем «Список» и в поле «Источник» вводим названия диапазонов (Мебель;Техника)

Далее для второго зависимого списка тем же способом для ячейки «Вид» в поле «Источник» вводим формулу =ДВССЫЛ($B$14), а для «Тип» =ДВССЫЛ($B$15). В результате у нас получится следующее:

3. С помощью функций СМЕЩ и ПОИСКПОЗ

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

Далее нажимаем Ctrl+F3, где задаём имя диапазону D1:D3 (Товар). Затем на вкладке «Данные» — «Проверка данных» — «Тип данных» — «Список» и в строке «Источник» указываем =Товар или просто выделить ячейки D1:D3 (если они на том же листе, где список).

Чтобы выпадающий список автоматически пополнялся новыми данными из категории «Товар», открыв «Диспетчер имён» в строке диапазон вписываем формулу =СМЕЩ($D$1;0;0;СЧЁТЗ($D$1:$D$400);1) где $D$400 количество ячеек необходимое для образца выпадающего списка.

Для зависимого списка товара создадим именованный диапазон с функцией СМЕЩ, который будет динамически ссылаться только на ячейки товара определенного вида. Для этого нажав Ctrl+F3 создаём новый именованный диапазон с любым именем (например, Вид) и в поле «Диапазон» в нижней части окна вводим следующую формулу:

Ссылки должны быть абсолютными (со знаками $). После нажатия Enter к формуле будут автоматически добавлены имена листов.

Функция СМЕЩ выдаёт ссылку на диапазон нужного размера, сдвинутый относительно исходной ячейки на заданное количество строк и столбцов. Вот так: =СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; размер_диапазона_в_строках; размер_диапазона_в_столбцах)

Таким образом:

— начальная ячейка – берём первую ячейку нашего списка, т.е. А1

— сдвиг_вниз – считает функция ПОИСКПОЗ, которая выдаёт порядковый номер ячейки с выбранным товаром (G2) в заданном диапазоне (столбце А)

— сдвиг_вправо = 1, т.к. мы хотим сослаться на Вид в соседнем столбце (В)

— размер_диапазона_в_строках – вычисляем с помощью функции СЧЁТЕСЛИ, которая умеет подсчитать количество встретившихся в списке (столбце А) нужных нам значений – товара (G2)

— размер_диапазона_в_столбцах = 1, т.к. нам нужен один столбец с Видом товара

В итоге у вас должно быть так:

661 пост 14.8K подписчиков

Правила сообщества

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях

Важно — сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

Утверждения вроде «пост — отстой», это оскорбление автора и будет наказываться баном.

Поставил + и сохранил. Спасибо!

А кто знает как такое же сделать в гуглдоке? Как в экселе гуглится легко, а в Гуглдокс не нашла:(

Читать ещё на Пикабу

5 вещей, которые НЕ НУЖНО делать в Excel (и как делать нужно). Часть 1⁠ ⁠

Друзья, всем привет.

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

Важное примечание!

Сразу оговорюсь, что речь здесь скорее пойдёт не про то, как исправлять уже кривые данные, а про то, как правильно первоначально их оформлять. Так что статья больше ориентирована не на тех, кто работает с готовыми данными, а собирает все свои таблицы «с нуля».

1 — Объединение ячеек.

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

Ячейки в заголовках, где написаны месяцы, объединены. Если мне нужно будет просуммировать продажи за январь-февраль, то при переходе с 9 на 10 строку диапазон будет расширен до неприличного и ненужного мне. Придётся либо вручную его менять, либо через точку с запятой указывать несколько отдельных диапазонов.

Как сделать лучше.

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

Ничего не объединяя, пишем название месяца в первую ячейку (В2, если речь про ЯНВАРЬ), далее выделяем ячейки, в которых январь должен быть виден (В2:F2) — правая кнопка мыши — Формат ячеек (или CTRL + 1 с клавиатуры):

Далее вкладка Выравнивание — по горизонтали — по центру выделения:

К остальным заголовкам можно уже применить «Формат по образцу» (кстати, для многократного применения формата нужно нажать на иконку два раза левой кнопкой мыши), чтобы не проделывать все действия заново. Визуально получим то же самое, но без богомерзкого объединения ячеек.

2 — «Правильная» таблица

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

Красиво, хорошо, душа радуется. Но грусть нас постигнет, если мы захотим потом построить сводную таблицу на основе этих данных. Нет, я не говорю, что это невозможно. Построим. И даже худо-бедно сможем годы просуммировать или месяцы. Но если бы таблица содержала все 12 месяцев или период не в 3 года, а лет в 5-6 хотя бы, то всё было бы куда хуже.

Как сделать лучше.

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

Теперь и сводную строить сплошное удовольствие, и сортировать/фильтровать данные куда проще. Про то, как трёхмерные таблицы переделывать в плоские не вручную, здесь речь не пойдёт.

3 — «Правильные» даты

Сразу заявлю, что я понимаю боль тех, кто сталкивается с уже волшебно введёнными датами. Но речь про то, как делать это правильно изначально. С датами в Excel вообще всё весело. Я хочу рассказать про самый, с моей точки зрения, чудесный способ ввод дат. Делаем мы таблицу, дошло дело до дат. И мы, как ответственные, трудолюбивые работники выбираем ячейку, и, как мама учила, начинаем вводить «29 марта 2023 г.». Целый день так работали в поте лица, устали. Довольные проделанной работой и гордые за себя. Но если вдруг нас просят отфильтровать данные только по одному конкретному месяцу или посчитать разницу между двумя датами, то мы впадаем в депрессию, потому что «тупой иксель» не фильтрует, не считает:

Ну не умеет он так даты воспринимать. Все мы неидеальны.

Как сделать лучше.

Вводить даты, используя православные разделители, которые Excel понимает. Их три:

«-» — дефис/тире/минус (кому как удобнее)

А потом уже через формат ячеек установить то, что тебе нужно.

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

4 — Цветовое оформление таблицы.

Есть у нас унылые данные, никак не оформленные, а душа требует праздника и цвета. Начинаем аккуратно выделять столбцы и строки, усердно закрашиваем. Кто-то так вообще не церемонится, и выделяет сразу весь столбец и уверенно нажимает на ведро с краской:

А что? А вдруг новые данные появятся, я вот сразу этот момент продумал. Я — молодец. Потомки спасибо скажут. Нет, не скажут. Во-первых, это некрасиво. Во-вторых, это лишняя нагрузка на сам Excel. В-третьих, есть способ куда удобнее и практичнее.

Как сделать лучше.

Форматировать данные как таблицу.

Встаём в любую ячейку таблицы, Главная — Стили — Форматировать как таблицу. Выбираете цвет своего настроения (если ничего не хотите, то потом можно поменять или выбрать Создать свой стиль таблицы), проверяем диапазон и галочку «Таблица с заголовками» — Ок. Всего 4 (. ) нажатия на кнопку мыши и готово. В этих наших интернетах это называется «умная» таблица. Инструмент обсуждаемый. Кто-то их боготворит, кто-то проклинает. Про всё здесь не расскажешь (наверное, отдельную статью писать придётся). Затрону только пару вещей. «Умная» таблица резиновая. То есть все столбцы и строки, которые мы будем добавлять вручную или копированием и вставкой, сразу будут отформатированы как и всё остальное в столбцах (включая формулы и форматы). Выделить столбец можно либо встав в любую ячейку столбца и нажав CTRL + пробел, либо наводим курсор на верхнюю часть ячейки с заголовком, ловим момент, когда он превратится в чёрную (чёрные стрелки важны) стрелку вниз, и нажимаем левую кнопку мыши.

5 — Ручное закрашивание данных

Нужно нам красивый табель сделать. Есть сотрудники, есть дни недели. Рабочие дни усердно и кропотливо закрашиваем через ведро с краской. Получается вот так:

Вроде бы, и хорошо. А как теперь посчитать, сколько человеки наши в неделю работали? Встроенные функции по цветам считать не умеют. Можно, конечно, пользовательскую функцию написать на VBA, но.

Как сделать лучше.

Условное форматирование плюс немного магии пользовательских форматов. В ячейки, где человек работал, ставим «1». Выделяем весь диапазон, где хотим что-то закрашивать. Главная — Стили — Условное форматирование — Правила выделения ячеек — Равно. Равно 1, в окошке справа выбираем пользовательский формат. На вкладке «Заливка» выбираем нужные нам цвета. А вот самое интересное происходит на вкладке «Число». Выбираем «(все форматы)», в поле «Тип» прописываем следующий формат «;;;» (без кавычек три точки с запятыми). Если кратко, то каждой точкой с запятой мы задаём формат для положительных, отрицательных, нулевых и текстовых значений. А так как ничего конкретного не прописали, Excel никак данные отображать и не будет. То есть физически в ячейке будет «1», но визуально ячейка будет пустой. Мало того, что мы теперь можем подсчитать количество рабочих дней с помощью СУММ или СЧЁТ, так ещё и при внесении изменений всё у нас будет меняться. Кстати, если делать это с «умной» таблицей, то при добавлении новых сотрудников созданное нами правило будет работать и на них.

На этом, пожалуй, первую часть закончу. Как всегда, огромное спасибо всем, кто осилил данный материал, потратив своё драгоценное время и внимание. Надеюсь, было полезно. Делитесь своими соображениями по поводу того, чего делать НЕ НУЖНО, работая с данными в Excel. Совместными усилиями будем искоренять нечестивые данные.

Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции⁠ ⁠

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

Важное уточнение! В Excel можно решать очень сложные задачи. Но должна быть хоть какая-то логика. Чем сложнее логика, тем сложнее будут сочетания функций, набор инструментов и т.д. Потратишь много времени, но решишь. А вот если логики нет, то не ждите чуда. Чудите сами, как говорится.

Что же, давайте приступим.

Есть столбец с текстом:

Нам необходимо в отдельные столбцы разнести код и наименование.

Первое, на что обращаем внимание, есть ли здесь закономерность? С кодом всё понятно. Нужно извлечь текст до первого пробела. Отлично. Находим порядковый номер первого пробела с помощью функции НАЙТИ (FIND):

Далее всё просто. С помощью функции ЛЕВСИМВ (LEFT) извлекаем всё, что до пробела, отнимая от порядкового номера пробела единицу:

Я показал пошаговое решение. Но это можно всё сделать и в одной формуле. Вместо D2 вставляем НАЙТИ (FIND):

Перейдём к более интересному — наименование. И снова начинаем с логики. Нужно извлечь текст между первым и последним пробелом. В Excel нет функции, которая по щучьему велению, по нашему хотению извлекает текст между первым и последним символом (например, пробелами). Придётся хитрить. Сначала пошагово, потом впихнём всё в одну ячейку (и оторвёмся по полной).

Надо подсчитать, а сколько в принципе у нас пробелов в тексте. Сделать это можно вот как:

ДЛСТР (LEN)(А2) (как вам название?) — подсчитывает общее количество символов в ячейке. ДЛСТР(ПОДСТАВИТЬ(A2;» «;»»)) — подсчитывает количество символов без пробелов. От них мы избавились благодаря функции ПОДСТАВИТЬ (SUBSTITUTE), которая заменила пробелы на пусто.

Теперь очень мудрёный шаг. Нам надо заменить последний пробел на какой-нибудь другой произвольный символ:

ПОДСТАВИТЬ (SUBSTITUTE)(A2;» «;»*»;E2) как раз это и делает. Даём ей исходный текст, что нужно найти, на что заменить, и какой символ по порядку (именно для этого нам нужно было узнать, сколько всего пробелов).

Зачем нам это? Немного терпения, друзья, сейчас всё будет.

Логика решения меняется. Теперь нам надо извлечь текст между первым пробелом и звёздочкой. А вот это реализовать уже можно. Находим порядковый номер звёздочки:

ПСТР (MID) — как много в этом слове. А функция, вообще-то, полезная. Указываем ей ячейку, порядковый номер, с которого хотим начать извлекать символы, и само количество символов.

А2 — понятно, ячейка с текстом.

D2+1 — начиная с какого символа будем извлекать (первый пробел + 1)

G2-D2-1 — количество извлекаемых символов. Порядковый номер звёздочки — порядковый номер первого пробела — 1 (чтобы исключить лишний символ в конце).

А теперь комбо! Всё в одной ячейке:

Говорил же, что будет страшно, а вы не верили.

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

В файле, который идёт к этой статье, есть ещё один готовый пример. Это тоже вполне себе реальная задача, которую слушатели предложили решить во время корпоративного обучения. Исходный текст нужно было переделать в то, что указано в «Должно быть»:

Здесь уже расписывать не буду, а то ещё одна «простыня» получится. Кому интересно, посмотрите. Там, правда, уже не так всё подробно по шагам расписано. Но я в вас верю!

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

Как всегда, огромное всем спасибо, кто потратил своё драгоценное время и внимание на чтение данного материала. Надеюсь, что-то вам обязательно пригодится. Пишите комментарии, делитесь своими мыслями, решениями и вопросами. Да пребудет с нами сила ИКСэль.

П.С. недавно проводил мастер-класс по разным штукам в Excel, про которые здесь почти не писал. Например, рассказал про то, что такое расширенный фильтр.

Мгновенное заполнение в Excel — магия в чистом виде⁠ ⁠

Друзья, всем привет. Сегодня хочу рассказать вам про мгновенное заполнение в Excel.

Ссылка на файл, чтобы можно было потренироваться — https://disk.yandex.ru/i/HyW0N215F6CuUg

Возможно, многие с ним знакомы заочно. Наверняка же замечали, что когда вручную заполняешь какие-то значения в ячейках, то с переходом к следующей ячейке при вводе символов Excel порой выдаёт вот такой список:

Так вот это и есть мгновенное заполнение во всей своей красе. Да, иногда это раздражает, потому что тебе это не нужно. Но в большинстве случае польза мгновенного заполнения огромна.

Извлечение данных

Предположим, у нас есть вот такой столбец с текстом:

Нам нужно извлечь отдельно номер договора и дату. Это можно сделать с помощью инструмента «Текст по столбцам». Правда, потом придётся от символа «№» ещё избавляться. А вот мгновенное заполнение справится с этим намного быстрее. Просто вводим справа от текста в первую ячейку номер договора (1), нажимаем Enter. Далее возможны два варианта.

Вариант 1. Вручную вводим в ячейку первую цифру второго договора (2). Excel предлагает свои варианты, жмём Enter — PROFIT!

Вариант 2. После того, как перешли ко второй ячейке, сразу нажимаем сочетание Ctrl + E (Е английская, конечно). Именно это сочетание отвечает за запуск мгновенного заполнения. Аналогично с датами. Вводим в ячейку С2 дату первого договора — Enter — Ctrl + E — наслаждаемся результатом.

ОЧЕНЬ ВАЖНАЯ ЧАСТЬ СТАТЬИ.

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

Ух ты! И так будет работать всегда?! Строго говоря — нет. Иногда, Excel не может с одной ячейки распознать логику. В этом случае нужно вручную заполнить не одну, а две, три, четыре (если случай совсем запущенный) ячейки. И только после этого нажимать Ctrl + E. Чем больше ячеек заполняешь, тем выше вероятность того, что твоя логика будет верно распознана могучим интеллектом Excel. Порой мгновенное заполнение не справляется с поставленной задачей:

Даты в первом столбце указаны в формате ГГГГ-ММ-ДД. При попытке привести их в формат ДД-ММ-ГГГГ получается вот такая «красота». Поэтому не поленитесь после того, как все ячейки будут заполнены, пробежаться по ним, а тот ли в них результат, который ты ожидал увидеть.

Образцы вводите в соседнем столбце от источника (можно справа или слева). Не «убегайте» далеко от данных, результат может быть непредсказуемым или вообще ничего не будет.

Ещё одно важное дополнение: мгновенное заполнение работает в версиях Excel 2013 и выше.

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

Извлечение только чисел из столбца

Если нам из «красивого» столбца, в котором есть значения вроде «123руб», «55 рублей» и так далее, нужно извлечь только цифры, то вы уже знаете, что нам поможет:

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

Работа с текстом

В столбце указаны Имя и Фамилия. Нам нужно получить результат в виде «Имя Ф.» В первой ячейке вводим образец — Enter — Ctrl + E:

Кстати, если попробовать получить Фамилия И., то будьте внимательны. Если прописать два примера, потом начать вводить третий, то появляется довольно забавный список:

Но если не начинать вводить в третью ячейку текст, а сразу нажать на Ctrl + E, то всё будет нормально. Раз на раз не приходится. Временами мгновенное заполнение ведёт себя очень странно.

Извлечение части сплошного текста

Необходимо разбить слипшийся текст на части. Вводим в первых двух ячейках образец — Ctrl + E:

С номером поступаем аналогично.

Сбор текста

В отдельных столбцах есть различная информация, которую необходимо собрать в одно предложение. Обратите внимание, что порядок столбцов для мгновенного заполнения роли не играет. Прописываем предложение в первой ячейке — Enter — Ctrl + E:

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

В качестве небольшой рекламы позвольте оставить здесь ссылку на мастер-класс, который я буду проводить 9 марта. Кто хочет узнать ещё несколько полезных приёмов при работе в Excel (там почти не будет того, о чём я писал здесь), а ещё хочет услышать чуть больше про то, где я работаю, записывайтесь — Полезные приемы при работе в Excel. Часть 2 (specialist.ru)

На этом всё. Как обычно, спасибо огромное всем, кто потратил своё драгоценное время и осилил данное полотно. Надеюсь, было полезно. Видео по данной статье обязательно появится на моём канале — (36) Андрей Митрохин — YouTube

Таблица подсчета розеток/выключателей/рамок. ⁠ ⁠

Когда я работал в магазине электротоваров регулярно приходилось считать ЭУИ и рамки к ним по зарисовкам заказчиков или их работников, тогда я это делал на бумаге и неплохо набил на этом руку. Но у некоторых продавцов консультантов это выходит не слишком быстро и качественно. Для автоматизации процесса я решил создать таблицу в google, а затем перенес ее в Excel (последний мне нравится больше). Таблицей я намерен поделиться ссылки будут ниже, а пока краткое описание:

Это страница «Сводка» первоначально ее надо заполнить под себя и сохранить как шаблон:
— Наименования всех типов ЭУИ какие у вас могут быть (если не достаточно того, что ввел я)
— Цвета механизмов (или например серия + цвет, как удобнее будет)
— Цвета рамок (аналогично механизмам)

Потом переходим на страницу «Ввод данных»
При добавлении новой строки указываете комнату
цвет механизма, цвет рамки и наполнение постов выбираете из выпадающего списка (подтянутся варианты со страницы «Сводка»), когда вы выбираете механизм для поста — ячейка окрашивается, считая количество постов в рамке

После заполнения страницы «Ввод данных», возвращаемся на «Сводка»
При выборе нужного цвета в крайней правой таблице («Текущий цвет») в списке ЭУИ и рамок останется только то количество, которое соответствует выбранным цветам.
Кроме того, общее количество механизмов и постов в рамках и количество установочных коробок

ЗЫ Отдельное спасибо @XaXa3Pa3a

Полезные трюки при работе в Excel⁠ ⁠

Всем привет. Это моя первая статья на Пикабу, поэтому позвольте сначала представиться. Я являюсь преподавателем Microsoft Excel. Теперь, когда с формальностями покончено, можно перейти к основному.

Сомнения перед написанием

Я довольно часто читаю разный тематический материал на Пикабу, и меня восхищают большинство авторов и статей. Статьи восхищают, в первую очередь, своей интересностью (есть такое слово вообще?) и полезностью. Именно поэтому у меня были большие сомнения, а стоит ли вообще лезть со своими очередными «простыми, но полезными штуками при работе в Excel». Да и кому вообще ты со своим Excel нужен?! Тем более, что беглый поиск по сайту не выдал ни одной подобной статьи. И та часть меня, которая отвечает за неуверенность, сразу подметила, что раз нет, значит, оно никому не нужно. А может, просто плохо искал. И да, я отдаю себе отчёт в том, что подобного материала довольно много на просторах интернета. И всё-таки, принцип «лучше сделать и жалеть, чем не сделать вовсе» возобладал.

Почему я посчитал, что это будет полезно

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

Небольшое пояснение

Путь до той или иной команды обычно описывается следующим образом: название вкладки — потом группа команд — сама команда:

Если у вас ноутбук, то функциональные клавиши могут работать только при одновременном нажатии на кнопку Fn+F1-12 (есть такие ноутбуки, в которых и этот способ не работает, тут надо уже по модели ноута смотреть).

Вообще, почти каждая функциональная клавиша отвечает за какое-то действие. Но я остановлюсь на одной, а именно — F4. И нет, речь пойдёт не про то, что этой кнопкой в Excel мы можем менять тип ссылки для ячейки.

F4 — повтор последнего выполненного пользователем действия (если нажимать её не тогда, когда курсор находится в строке формул)

Например, вам нужно для нескольких несмежных столбцов установить определённую ширину. Вместо того, чтобы каждый раз выбирать столбец, потом переходить на вкладку Главная — Ячейки — Формат — Ширина столбца. Можно один раз проделать эту операцию, потом просто выделить следующий столбец и нажать F4. И такой фокус можно проделывать со многими операциями, будь то закраска ячеек, строк, столбцов, части графика на диаграмме или банальная вставка столбцов (да, столбец можно вставлять сочетанием Ctrl + «+», но ведь это две кнопки, а F4 — одна).

Представления

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

И вот вы каждый раз раскрываете фильтр, устанавливаете нужные критерии, просматриваете данные, потом раскрываете фильтр, следующий критерий, потом фильтр. Думаю, суть вы уловили. «Но всё меняется, когда приходят они — представления!» © Установив нужные критерии, переходим на вкладку Вид — Режимы просмотра книги — нажимаем Представления:

Далее всё интуитивно (куда же без интуиции в этой прекрасной программе) понятно. Жмёшь «Добавить», обзываешь представление так, как тебе угодно — Ок. Здесь же, в окне добавления представления, мы можем узнать, а что, собственно, Excel сохраняет. А сохраняет он параметры печати, результаты фильтрации, скрытые строки и столбцы. Создав под каждый набор фильтров, строк и столбцов представление, потом лёгким и непринуждённым нажатием на эту команду ты будешь менять свою таблицу в мгновение ока. Это не совсем удобно? Что же, согласен. Давайте сделаем ещё удобнее и добавим представления на панель быстрого доступа. Для этого раскроем настройку панели быстрого доступа — Другие команды:

В открывшемся окне в поле «Выбрать команды из:» выбираем «Все команды». Потом находим «Представления» — Добавить:

Кстати, так можно добавить на панель быстрого абсолютно любую команду.

Теперь у нас появился выпадающий список со всеми нашими сохранёнными представлениями. Через это же окно можно и новые представления создавать. Просто пишешь в нём название, нажимаешь Enter — готово.

ПРЕДУПРЕЖДЕНИЕ!

Представления не работают в книгах, в которых есть «умные» таблицы (таблицы, которые мы создаём через вкладку Главная — Стили — Форматировать как таблицу).

После создания представления не нужно перемещать столбцы/менять их местами, иначе представление прекратит работать.

Два окна одной книги.

Прежде, чем кидать в меня различные предметы с криками «мало того, что про какой-то Excel пишет, так сейчас ещё будет рассказывать, как в двух окнах работать, смерд?!» позвольте пояснить. Речь пойдёт о том, как работать в двух окнах с ОДНОЙ книгой. Давайте смоделируем ситуацию. Есть у тебя два монитора (если ещё нет, обязательно заводи второй, пускай небольшой, но чтобы был), один файл Excel с несколькими листами внутри. Тебе нужно из одной таблицы перенести данные в другую (сравнить их, связать формулами и так далее). Что ты делаешь? Правильно, бесконечно долго и уныло переключаешься между листами. Второй монитор тем временем грустно за этим наблюдает. Но можно сделать этот процесс более удобным и быстрым. Прошу любить и жаловать, вкладка Вид — Окно — Новое окно:

Нажав на эту команду, мы получим ту же самую книгу, но в другом рабочем окне. Название файла будет немного изменено на «Мой файл:1» и «Мой файл:2». А дальше уже дело за тобой. Располагай окна так, как тебе удобно (на одном мониторе, на разных), копируй данные, создавай связи, формулы — в общем, работай. Но делать ты это уже будешь быстрее и удобнее. Все изменения, которые мы вносим в любое из клонированных окон, появляются сразу во всех связанных окнах. Главное, не забыть нажать «Сохранить» хоть в каком-нибудь окне.

Специальная вставка (пропускать пустые ячейки)

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

Нужно перенести данные из крайнего правого столбца второй таблицы (столбец Р) в крайний столбец первой таблицы (столбец F) таким образом, чтобы существующие номера остались. Обычным копированием-вставкой сделать это не получится, так как в столбце Р есть пустые ячейки, которые заменят собой существующие номера в столбце F. И тут на сцену выходит специальная вставка. Выделяем диапазон из столбца Р, копируем. Далее выбираем ячейку, начиная с которой нужно вставить данные (в нашем случае это F2), и либо щёлкаем правую кнопку мыши — в контекстном меню ищем «Специальная вставка», либо нажимаем сочетание клавиш Ctrl+Alt+V. Попадаем в такое окно:

Ставим галочку рядом с «пропускать пустые ячейки» — Ок. Профит!

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

P.S. Если статья покажется интересной и полезной, то на примете есть ещё несколько приёмов, про которые могу рассказать.

Друзья, создал на Ютубе свой канал. Пока только видео с первой статьёй. В ближайшие дни опубликую вторую часть. Полезные трюки и приёмы при работе в Microsoft Excel — YouTube

Если б мишки были пчёлами… условия в Excel⁠ ⁠

Решил попробовать формат коротких постов по Excel

Дети, сегодня я расскажу вам о такой удобной функции в Excel как ЕСЛИ.

В общем виде выглядит так:

ЕСЛИ (условие; результат если условие верное; результат если условие не верное)

Я буду писать формулу, а на картинке будет результат этой формулы в 5 столбце

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

=ЕСЛИ(И(A2=»овощи»;D2>50);»дорогой овощ»;»либо не дорогой, либо не овощ»)

Не забываем, что можно комбинировать несколько ЕСЛИ Главное не запутаться в скобках и точках с запятыми.

=ЕСЛИ(A2=»фрукты»;ЕСЛИ(D2>100;»дорогой фрукт»;»не дорогой»);»не фрукт»)

Как видим результат получается довольно гибкий.
Также напомню, что есть операторы сравнения <,>,<=,>=,=,<>.

А на этом сегодня все, пойду дальше писать про макросы…

Ответ на пост «Помощь в формировании Таблицы на основе 2 таблиц»⁠ ⁠

Если по-простому, формулами, то так:

Таблицу 3 переделываем в другой вид:

Это будет состав детали, даты будем добавлять вправо.

(Таблица соответствия количества материала количеству детали)

Таблица 2 тогда будет выглядеть так:

(тупо суммируем кол-во материалов на дату)

1. Важно чтобы во всех 3-х таблицах даты были синхронизированы, чтобы можно было протягивать формулы не опасаясь что-то поломать.

2. Важно следить чтобы в составе (таблица 3) не было дубликатов пар — [деталь — материал] (желтые столбцы), иначе объем задваиваться будет.

Сумма итогов, чтобы её не переписывать каждый раз (в ячейке H2) — может выглядеть так:

Ответ на пост «Excel, делим покупки на несколько человек»⁠ ⁠

И так господа, перед Вами методичка по распиливанию затрат после попоек.

Разработана она мною, но на копирайт не претендую.

Ситуация проста: Пятница, Вы собрались пошуметь. Заказаны «стартовые» места, собрана компания друзей, сняты ограничения с карточек, побрились, подмылись, вызвали такси и поехали!

Сразу рекомендация: договоритесь как будете делить счёт. Вполне возможно, что в вашей компании тот, кому завтра платить за ипотеку. И он вообще в принципе не собирается много тратить.

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

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

Помните! Очень большая вероятность, что вы вчера платили за всех и вам все должны (что не факт). В этом случае не один из ваших друзей чисто морально не хочет к похмелью ещё добавлять чувство долга! Поэтому от того, как быстро вернёте деньги зависит только от вас. Теперь вы коллектор и начинаете обзвон друзей.

1. Кидаем в чат позывной. Кто, за что, вчера платил.

2. Открываем Excell и создаём следующую таблицу:

И так: Перед вами таблица со следующими параметрами:

Столбец «Событие» — кратко фиксим события оплаты;

Столбцы «Участники» — все, кто присутствовал на пьянке;

Столбец «Сумма» — Общая сумма к разделу;

Столбец «N» — кол-во человек участвующих в разделе;

Столбец «Проверка» — собственно сумма всех сумм в столбцах B-E = 0.

Если пьянка длилась несколько дней (например Нашествие), то слева ещё вставляется таблица «Дата».

Допустим начали сначала Вася, Петя, Игорь. Зашли в Кафе, пожрали, да выпили на 9500 рубликов. Платил Петя.

Тогда для Пети пишется формула:

Для всех, кто учувствовал пишется формула:

В результате получаем:

Все кто с минусом — те в долгах;

Кто с плюсом -тот кредитор.

Формулы с $ помогают просто копипастить ячейки, и заполняя только столбцы Сумма и N, долбить долг дальше по событиям:

Как мы видим, в пабе к нам пришёл ещё Ваня.

Дальше из караоке идём в клуб. Ваня цепляет тёлку, Вася уезжает домой.

Для этого вводим ещё один столбец а с Васей прощаемся. В клубе мы оставляем 15 к.

Ваня с «Ваней — 2» уезжают, а Петя и Игорь идут:

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