Эта формула вычисляет, сколько раз имя сотрудника встречалось в диапазоне с именами.

2. Далее выявим свободных сотрудников. Добавим ещё один столбец и введём в него формулу, которая будет выводить номера свободных сотрудников:

=ЕСЛИ(F2-G2<=0;"";СТРОКА(E2)-СТРОКА($E$2)+1)

=IF(F2-G2<=0;"";ROW(E2)-ROW($E$2)+1)

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

=ЕСЛИ(D2>СЧЁТ($H$2:$H$10);"";ИНДЕКС($E$2:$E$10;

НАИМЕНЬШИЙ($H$2:$H$10;СТРОКА(E2)-1)))

=IF(D2>COUNT($H$2:$H$10);"";INDEX($E$2:$E$10;

SMALL($H$2:$H$10;ROW(E2)-1)))

Эта формула выводит очередное по номеру имя сотрудника (используя функцию НАИМЕНЬШИЙ) из списка или пустую ячейку, если имена свободных сотрудников уже кончились.

4. Далее создаём именованный диапазон сотрудников нажав Ctrl+F3 или на вкладке «Формулы» - «Диспетчер имён», где из столбца списка E2 зададим название «Имена», а в строке «Диапазон» введём формулу:

=СМЕЩ(Лист1!$I$2;0;0;СЧЁТЗ(Лист1!$I$2:$I$10)-СЧИТАТЬПУСТОТЫ(Лист1!I$2:I$10))

=OFFSET(Лист1!$I$2;0;0;COUNTA(Лист1!$I$2:$I$10)-

COUNTBLANK(Лист1!I$2:I$10))

5. Создадим выпадающий список выделив ячейки B2:B8 и на вкладке «Данные» - «Проверка данных» - «Список» - «Источник» =Имена

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

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

Теперь создадим первый выпадающий список для выбора певца. Выделяем пустую ячейку и на вкладке «Данные» нажимаем кнопку «Проверка данных». Затем из выпадающего списка «Тип данных» выбираем вариант «Список» и в поле «Источник» выделяем ячейки с названиями (ячейки 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 количество ячеек необходимое для образца выпадающего списка.

=OFFSET($D$1;0;0;COUNTA($D$1:$D$400);1)

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

=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)

=OFFSET($A$1;MATCH($G$7;$A:$A;0)-1;1;COUNTIF($A:$A;$G$7);1)

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

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

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

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

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

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

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

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

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

Добавляем выпадающий список на основе созданной формулы к ячейке G3, нажав на вкладке «Данные» команду «Проверка данных», где выбираем «Список» и в качестве «Источника» указываем =Вид

2 способ

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

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

В результате будет выходить такое сообщение:

Удалить выпадающий список можно выбрав в «Тип данных» – «Любое значение».

С помощью макроса (с возможностью добавления новых элементов)

Допустим, что у нас есть список с именами и таблица, куда их нужно вносить.

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

Выделяем весь список (ячейки A1:A7) и нажимаем Ctrl+T или Главная - Форматировать как таблицу, чтобы создать "умную" таблицу:

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

После переименования таблицы нажимаем Enter, в результате слово «Легенды» исчезнет, но диапазон будет создан.

Проверить диапазон можно на вкладке Формулы - Диспетчер имен:

Таким образом, при дописывании новых имен к списку будет расширяться наша "умная" Таблица3, а за ней и наш именованный диапазон Легенды.

Теперь создаём выпадающий список в ячейке

Выделяем зелёные ячейки и жмём на вкладке "Данные" кнопку "Проверка данных".

Далее выбираем из выпадающего списка "Тип данных" позицию "Список" и вводим в строку" Источник" ссылку на созданный именованный диапазон =Легенды:

Чтобы Excel позволил нам в будущем ввести в список новые имена, снимем галочки на вкладках "Сообщение для ввода" и "Сообщение об ошибке" и жмём ОК. Выпадающий список готов!

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

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

Щёлкаем правой кнопкой мыши по ярлыку нашего листа и выбираем "Просмотреть код". В модуле листа в редакторе Visual Basic, скопируйте код (не забудьте заменить название диапазона и ячейки ввода, также можно изменить вопрос при добавлении:

Private Sub Worksheet_Change(ByVal Target As Range)

Set p = Range("Легенды")

If Target.Cells.Count > 1 Then Exit Sub

If IsEmpty(Target) Then Exit Sub

If Not Intersect(Target, Range("D2:D11")) Is Nothing Then

If WorksheetFunction.CountIf(p, Target) = 0 Then

r = MsgBox("Добавить новое имя в справочник?", vbYesNo)

If r = vbYes Then p.Cells(p.Rows.Count + 1) = Target

End If

End If

End Sub

Теперь при попытке ввести новое имя в любую из зелёных ячеек Excel будет спрашивать:

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

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

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

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

Телефон был назван вслух два раза, один раз при оформлении заказа, второй раз на кассе.

На второй день после покупки, на телефон девочки в whats'app приходит сообщение от якобы мальчика живущего в соседнем дворе, которому не с кем играть и он хочет познакомиться с девочкой. Девочка сразу сообщила маме, что кто-то пишет ей с чужого номера, который она не знает. Попытки прозвонить номер не увенчались успехом, трубку просто не брали. На вопрос в переписке, откуда номер, отвечали, что-то вроде твоя подружка дала. Как зовут подружку? Отвечали - не знаю, она со мной не захотела дружить.

Мама забрала телефон у девочки, пока не разберется. Попыталась написать заявление в полицию, но, получила отказ из-за отсутствия состава преступления.

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

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

Еще через пару дней поступило сообщение вроде такого - "Мне сегодня папка рассказал, чем мальчик отличается от девочки, давай проверим", а потом "пришли мне свою фотографию без трусиков, а я тебе свою".

Есть состав! Заявление в полицию, "мальчика" нашли, ему 29 лет.

Анализ, выводы, рекомендации.

В ходе следствия выяснилось, что "мальчик" не имеет отношение к магазину, является простым покупателем, услышал и запомнил номер (или часть номера) девочки на кассе или на стойке регистрации заказа, просто был рядом, когда назвали номер.

В whats'app у ребёнка стояла ее реальная фотография, что помогло(бы) найти по полному или частичному номеру, перебором.

Не устанавливайте своим детям мессенджеры, без особой необходимости. Не допускайте использования в профилях и аватарах РЕАЛЬНОЙ информации и фотографий. Постарайтесь объяснить детям, что, если пришло сообщение с незнакомого номера - нужно или проигнорировать его, или, лучше, сообщить родителям. Не называйте номер своего ребенка в общественных местах, вариантов на самом деле масса, привести все - невозможно. Я не призываю к педоистерии, просто будьте внимательнее к персональным данным своих детей! Будьте осторожны! Чуть позже будет анализ еще одной ситуации с возможной утечкой данных.

Всем спасибо!

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

P.P.S.: Пруфов не будет. Вносите кота с лампой!!!

Без рейтинга!

1. Проездным документом не является. Значит, несмотря на то, что я оплатил проезд, предъявить этот документ для подтверждения проезда я не могу, хотя все реквизиты проездного документа вроде как есть.

2. Абонемент на 5 дней и указаны четкие даты. Значит я не могу пользоваться проездом в другие даты.

3. Номер карты "Тройка" на которую "записан" билет.

4. ВНИМАНИЕ! Ошибка оформления. Обратитесь к старшему кассиру. Вот эту хрень мелкими буквами я не успел прочитать, потому что спешил на электричку.

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

1. Они не могут записать поездки на карту. Никак. Вообще.

2. Они предлагают мне сдать карту и написать заявление о возврате средств. Через 14 дней. Забрав карту с поездками метро, Карл! А взамен - оформить новый абонемент.

3. Они не хотят пускать меня по документу, который подтверждает факт оплаты услуги.

Вот так, друг мой, можно взять с пассажира деньги и кинуть его с оплаченной услугой. Несмотря на то, что:

В правилах проезда самой "МТППК" сказано:

3.1. Для проезда в поезде пригородного сообщения пассажир обязан приобрести проездной документ (билет) для разовой поездки «Туда» или «Туда и обратно», либо абонементный билет установленной формы.

И это сделано.

В Гражданском кодексе:

ГК РФ Статья 786. Договор перевозки пассажира.

1. По договору перевозки пассажира перевозчик обязуется перевезти пассажира в пункт назначения, а в случае сдачи пассажиром багажа также доставить багаж в пункт назначения и выдать его управомоченному на получение багажа лицу; пассажир обязуется уплатить установленную плату за проезд, а при сдаче багажа и за провоз багажа.

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

А вы как думаете, господа домашние юристы и диванные эксперты?

Будьте внимательней с этой автоматизацией.