MS, Libreoffice & Google docs

Ты уже наверняка создавал выпадающие списки в Excel. Делается это очень просто: выбираем ячейку, в которой нужен выпадающий список (1), идём в Данные -> Проверка данных (2).

В открывшемся окне, в поле «Тип данных» выбираем «Список» (3), и затем кликнув в поле «Источник» выделяем данные с рабочего листа для выпадающего списка (4). Подтверждаем нажатием на «Ок» (5).

Теперь при нажатии на символ с выпадающего списка мы можем выбирать один из определенных в источнике вариантов.

Однако, проблема заключается в том, что созданный таким образом выпадающий список является статичным. То есть если в таблице появится еще одна строка, выпадающий список останется прежним – соответственно неполным.

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

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

Ну и подтверждаем ввод нажатием на "ОК". Всё, всё готово! Теперь наш выпадающий список всегда будет актуальным, не важно были удалены из него определенные элементы или же добавлены новые - обязательно испробуй такой вариант выпадающего списка!

Вот в этом видео я показываю всё рассказанное, и, кроме того, делюсь и другими важными советами и подсказками по созданию динамических выпадающих списков, по работе со структурированными ссылками, а также о функции ДВССЫЛ:

Приятного просмотра! 😊

Наверняка ты при работе в Excel уже замечал выскакивающие иногда попытки программы помочь тебе в заполнении того или иного столбца – это так называемое «Мгновенное заполнение» (Flash Fill), работающее для распознавания шаблонов в твоих действиях.

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

1. Скопируй весь этот столбец с сокращёнными названиями месяцев и вставь в рабочем листе Excel, чтобы вышло как у меня в демонстрации:

янв

фев

мар

апр

май

июн

июл

авг

сен

окт

ноя

дек

2. Пропиши в соседней (слева или справа) от «янв» ячейке  полное название месяца «Январь»

3. Пока ты писал слово «Январь», Flash Fill уже искал закономерности в том, что ты делаешь. И сейчас ты в этом убедишься: Переходи в ячейку ниже и также начинай прописывать слово «Февраль». А вот и врывается в игру «Мгновенное заполнение», правда, достаточно интересным способом. Если нажимаем на Enter, то Excel «протягивает» предложенное решение:

В итоге мы имеем январь, феварь, марарь, апрарь, майарь, июнарь, июларь, авгарь, сенарь, октарь, нояарь и декарь.

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

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

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

Кроме того, если ты хочешь узнать еще больше разных советов по работе в Excel (и особенно по автоматизации с помощью VBA), то приглашаю тебя подписаться на мой канал на YouTube! Вот, например, плейлист по шорткатам / фишкам и особенностям работы с функциями и формулами:

А это новый курс по VBA для новичков:

До встречи! 😊

P.S. Комбинация клавиш для того, чтобы заставить Excel попробовать найти шаблоны: CTRL + E

Извлечь числа из строки текста в Excel, естественно можно с помощью формул. Например, в этом может помочь следующая формула массива:

Тем не менее, у использованной выше формулы есть определенные минусы:

• Во-первых, все числа, например, из текста «Задача 5 от 19 Ноября» выдаются не разделёнными, образую таким образом одно слитное число, тогда же как информация о том, что числа на самом деле в оригинальном тексте разделены другими словами потенциально может быть важной.

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

Function extractDelimitedNumbers(ByVal strOriginalText As String) As String

Dim strExtractedNumbers As String

Dim lngTextLength As Long

lngTextLength = Len(strOriginalText)

Dim lngPositionCounter As Long

'Проверка, указано ли название файла

If strOriginalText <> "" Then

'Проверка каждой позиции названия

For lngPositionCounter = 1 To lngTextLength

'Если число...

If IsNumeric(Mid(strOriginalText, lngPositionCounter, 1)) = True Then

'... то сохраняем в переменную

strExtractedNumbers = strExtractedNumbers & Mid(strOriginalText, lngPositionCounter, 1)

'Разделение отдельно стоящих в названии чисел с помощью "_"

If lngPositionCounter + 1 <= lngTextLength Then

If IsNumeric(Mid(strOriginalText, lngPositionCounter + 1, 1)) = False Then

strExtractedNumbers = strExtractedNumbers & "_"

End If

End If

End If

Next lngPositionCounter

'Удаляем по итогу лишний нижний пробел, если таковой имеется

If Right(strExtractedNumbers, 1) = "_" Then

strExtractedNumbers = Left(strExtractedNumbers, Len(strExtractedNumbers) - 1)

End If

extractDelimitedNumbers = strExtractedNumbers

Else:

extractDelimitedNumbers = ""

End If

End Function

Как использовать этот код:

1. Открыть файл Excel, в котором нужно применить функцию (лучше его копию)

2. Открыть редактор VBA с помощью комбинации клавиш Alt+F11

3. В верхнем левом углу нажать на «Insert» и затем «Module».

4. Скопировать текст функции и вставить в открывшееся окно в центре редактора VBA

5. Сохранить файл в формате xlsm (формат xlsx не сохраняет макросы!). Для этого открываем окно сохранить как при помощи клавиши F12 либо File -> Save as -> Browse. По открытии окна сохранения файла в поле «Тип файла» выбираем «Книга Excel с поддержкой макросов»

6. Подтверждаем сохранение. Теперь функция может использоваться как самая обычная функция на рабочем листе Excel. То есть ставим знак равно, и прописываем название нашей пользовательской функции «extractDelimitedNumbers». В скобках указываем текст, из которого должны быть извлечены числовые значения:

Если тебе интересно узнать, как устроен принцип работы этой функции и как её можно написать самостоятельно, то в следующем видео я подробно об этом рассказал:

Конечно, для этого видео нужно уже владеть определенными знаниями VBA. Если ты еще совсем новичок в области VBA, то также могу предложить посмотреть вот этот курс VBA:

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

В первом столбце перечислялись напитки, а в оставшихся трёх были показаны цены трех возможных размеров стаканчиков – Tall (354 мл.), Grande (473 мл.), а также Venti (591 мл.).

Сравнивая цены различных предлагаемых напитков, я вдруг уловил себя на мысли, что при анализе цен мозг постоянно производит до боли знакомую операцию – ВПР из Excel! «Это же и есть классический вариант применения ВПР!» - подумал я.

Например, мы хотим заказать Кофе «Флэт Уайт» среднего размера, то есть «Grande». Вспомним синтаксис ВПР Excel:

ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)

Таким образом имеем следующее. Аргумент «искомое_значение» – это у нас напиток «Флэт Уайт». Таблица – это всё меню с предлагаемыми напитками. Номер столбца – это, считаем: раз, два, три – третий столбец, если начинать считать от первого столбца всего меню. Следовательно третий аргумент функции – тройка. Ну и последний аргумент – ЛОЖЬ, поскольку мы хотим заказать именно «Флэт Уайт», а не нечто похожее на этот напиток.

Производим расчёт функции в уме и узнаем, что заплатить нам придётся 255 рублей.

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

Вот такой интересный пример переплетения повседневности и работы в Excel.

Хороших выходных! 😊

P.S. Вот в этом видео я еще раз разобрал данный пример ВПР, только в видеоформате.

P.P.S. А вот в этом плейлисте Вы можете пошагово и в увлекательной форме (при этом структурированно и эффективно) изучить принцип работы с функциями и формулами Excel: