VBA.Excel

VBA.Excel

https://www.youtube.com/c/ExcelVBABillK/featured
На Пикабу
поставил 8 плюсов и 2 минуса
отредактировал 1 пост
проголосовал за 3 редактирования
Награды:
самый сохраняемый пост недели самый сохраняемый пост недели более 1000 подписчиков
48К рейтинг 4483 подписчика 4 подписки 56 постов 53 в горячем

8 инструментов в Excel, которыми каждый должен уметь пользоваться

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


1. Условное форматирование

2. Текст по столбцам

3. Фильтр и сортировка

4. Удаление дубликатов

5. Простой выпадающий список

6. Подсказка по вводу

7. Сводные таблицы

8. Примечания в ячейках


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

Так что обязательно советую всем новичкам в Excel ознакомиться с этими функциями! А что это был проще сделать, предлагаю посмотреть подготовленное видео как раз об этих инструментах:

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

Показать полностью 1

Начальник сказал очистить таблицу от тысячи пустых строк за час? Выполняем за 15 секунд!

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

Начальник сказал очистить таблицу от тысячи пустых строк за час? Выполняем за 15 секунд! Microsoft Excel, Лайфхак, Руководство, Видео, Длиннопост

Задача: у нас есть всего час, чтобы избавить таблицу от этих пустых строк. Естественно, вручную здесь никак не управиться, так что вот лайфхак.


Выбираем целиком один из столбцов, щелкнув по его заголовку:

Начальник сказал очистить таблицу от тысячи пустых строк за час? Выполняем за 15 секунд! Microsoft Excel, Лайфхак, Руководство, Видео, Длиннопост

Используем комбинацию клавиш CTRL+G и щелкаем по «Выделить»:

Начальник сказал очистить таблицу от тысячи пустых строк за час? Выполняем за 15 секунд! Microsoft Excel, Лайфхак, Руководство, Видео, Длиннопост

В следующем окне выбираем «Пустые ячейки» и нажимаем на ОК:

Начальник сказал очистить таблицу от тысячи пустых строк за час? Выполняем за 15 секунд! Microsoft Excel, Лайфхак, Руководство, Видео, Длиннопост

Excel выбрал все пустые ячейки, так что теперь используем комбинацию клавиш CTRL+- (контрол минус), в открывшемся окне выбираем опцию «Строку» и нажимаем на ОК:

Начальник сказал очистить таблицу от тысячи пустых строк за час? Выполняем за 15 секунд! Microsoft Excel, Лайфхак, Руководство, Видео, Длиннопост

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

Начальник сказал очистить таблицу от тысячи пустых строк за час? Выполняем за 15 секунд! Microsoft Excel, Лайфхак, Руководство, Видео, Длиннопост

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

Показать полностью 6 1

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query

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


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

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

Начинаем с того, что переводим этот список в формат умной таблицы (выбираем его и используем комбинацию клавиш CTRL+T):

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

Добавляем еще один столбец «Ссылка»:

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

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


Ищем в поиске (1) каждую акцию и открываем её страничку (2):

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

На рассматриваемом сайте данные по акциям за месяц хранятся на странице «Прошлые данные», так что щелкаем по её ссылке:

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

В нижней части этой страницы и находится таблица с нужными нам данными:

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

Копируем ссылку на указанную выше страницу и вставляем её в таблицу в Excel. Так заполняем всю табличку ссылками для каждой акции:

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

Теперь можно создавать запрос. Для этого копируем одну из подготовленных ссылок (неважно какую), и во вкладке «Данные» (1) щелкаем по кнопке «Из интернета» (2). В открывавшемся окне вставляем ссылку (3) и подтверждаем ввод (4):

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

В следующем окне Power Query предлагает к выбору все найденные на странице таблицы. Находим нужную нам (Это Table 1), выбираем её и нажимаем на «Преобразовать данные»:

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

В итоге открывается Power Query в нём мы видим нашу табличку:

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

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

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

Теперь переводим этот запрос в функцию. Открываем расширенный редактор:

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

Добавляем переменную (опять-таки подробнее рассказано об этом в видео) – это строка “(Link) as table =>”, и заменяем статичную ссылку на эту переменную:

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

Нажимаем на «Готово» и таким образом мы перевели запрос в функцию:

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

Сразу поменяем название этой функции (назову её fxStockData):

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

Теперь закрываем редактор Power Query, возвращаемся на рабочий лист и выбрав одну из ячеек умной таблицы с cсылками щелкаем во вкладке «Данные» по «Из таблицы/диапазона»:

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

Открывается редактор Power Query. В нём добавляем пользовательский столбец (Добавление столбца -> Настраиваемый столбец) со следующими формулой и названием:

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

Добавляется столбец Data, щелкаем по его символу развертывания (1), убираем галочку в «Использовать исходное имя столбца как префикс» (2) и нажимаем на ОК (3):

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

Всё! Удаляем ненужный столбец ссылками, он своё отработал и выгружаем на рабочий лист (Главная -> Закрыть и загрузить -> Закрыть и загрузить в… -> Имеющийся лист -> Нужная ячейка). В итоге на рабочем листе получаем следующий результат:

Импорт данных множества интернет-страниц в Excel с помощью одного запроса Power Query Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост, Power Query

Вот и всё! Всего одним запросом мы импортировали данные по всем интересующим нас акциям. Обновление данного запроса и расширение списка акций в нём – это дело всего одного клика. Об этом я рассказал в видео:

Обязательно советую его посмотреть, так как в нём я упоминал и объяснял множество важных, интересных аспектов. Приятного просмотра!

Показать полностью 19 1

Как изменить ориентацию лишь одного листа в Word

Небольшой пост-напоминалка о том, как повернуть в горизонтальное положение лишь одну страницу в Word-е.


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

Как изменить ориентацию лишь одного листа в Word Microsoft Word, Руководство, Видео, Длиннопост

Для этого нужно поставить курсор в самом начале второй страницы, вот так:

Как изменить ориентацию лишь одного листа в Word Microsoft Word, Руководство, Видео, Длиннопост

После чего во вкладке «Макет» нажимаем на символ в правом нижнем углу раздела «Параметры страницы»:

Как изменить ориентацию лишь одного листа в Word Microsoft Word, Руководство, Видео, Длиннопост

В открывшемся окне выбираем альбомную ориентацию листа, а ниже в поле «Применить к…» выбираем настройку «До конца документа»:

Как изменить ориентацию лишь одного листа в Word Microsoft Word, Руководство, Видео, Длиннопост

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

Как изменить ориентацию лишь одного листа в Word Microsoft Word, Руководство, Видео, Длиннопост

Поскольку нам нужна была альбомная ориентация лишь второй страницы, проделываем описанное выше с третьей страницей с настройкой книжной ориентации (ставим курсор в начале страницы, открываем окно настройки параметров страницы, выбираем книжную ориентацию и применяем настройку до конца документа) и получаем желаемый результат:

Как изменить ориентацию лишь одного листа в Word Microsoft Word, Руководство, Видео, Длиннопост

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

Показать полностью 6 1

Универсальный выпадающий список в Excel с поиском

Уже сегодня выпадающие списки с поиском в Excel в основном создают с помощью динамических массивов – быстро, просто, динамично. Единственная проблема заключается в том, что динамические массивы пока, к сожалению, доступны лишь пользователям Office 365, а стандартным пользователям без платной подписки они станут доступными скорее всего лишь в новом пакете Microsoft Office 2021-го года (где-то в октябре).


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


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

Расширяем функционал срезов сводных таблиц

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

Расширяем функционал срезов сводных таблиц Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост

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


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

Расширяем функционал срезов сводных таблиц Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост

Копируем мы для того, чтобы обе сводные были подключены к срезу. Убедиться в этом можем, щелкнув ПКМ по срезу, а затем нажав на «Подключения к отчетам»:

Расширяем функционал срезов сводных таблиц Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост

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

Расширяем функционал срезов сводных таблиц Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост

Теперь просто скрываем столбец с первой ячейкой сводной (в нашем примере столбец Е), поправляем ширину столбца с символом фильтра (в нашем примере столбец F) и помещаем в него примерно вот так срез:

Расширяем функционал срезов сводных таблиц Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост

Вот и всё. Теперь можно спокойной щелкать и по этому символу фильтра и искать нужные элементы. Вот, например, поиск всех видов масла из списка:

Расширяем функционал срезов сводных таблиц Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост

Подтвердив ввод, получаем выручку лишь по маслам:

Расширяем функционал срезов сводных таблиц Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост

Вот такой совет. Ссылка на файл из скриншотов для самостоятельного упражнения:


https://drive.google.com/file/d/1NXAZFNRMpCqfsQQLZWJ6x_rZl6s...


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

Показать полностью 6 1

Пользовательские числовые форматы Excel – с нуля до профи за 40 минут

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


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

В видео рассмотрено, пожалуй, всё - от каждого функционального символа кодов формата, вплоть до написания условий в структуре кода и т.д.


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

Пользовательские числовые форматы Excel – с нуля до профи за 40 минут Microsoft Excel, Руководство, Видео, Длиннопост

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

Показать полностью 1

Универсальный метод для выбора и заполнения пустых/определенных ячеек в Excel

Хочу поделиться универсальным методом выбора и заполнения пустых (и не только) ячеек определенным значением или формулой/функцией.


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

Универсальный метод для выбора и заполнения пустых/определенных ячеек в Excel Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост

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


Итак, что для этого нужно сделать? Во-первых, выбираем диапазон со значениями:

Универсальный метод для выбора и заполнения пустых/определенных ячеек в Excel Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост

Затем открываем окно поиска (CTRL+F):

Универсальный метод для выбора и заполнения пустых/определенных ячеек в Excel Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост

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

Универсальный метод для выбора и заполнения пустых/определенных ячеек в Excel Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост

Первый элемент списка уже выбран, это можно видеть по голубому выделению, поэтому сразу можем использовать комбинацию клавиш CTRL+A, чтобы выбрать все элементы списка:

Универсальный метод для выбора и заполнения пустых/определенных ячеек в Excel Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост

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

Универсальный метод для выбора и заполнения пустых/определенных ячеек в Excel Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост

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

Универсальный метод для выбора и заполнения пустых/определенных ячеек в Excel Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост

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

Универсальный метод для выбора и заполнения пустых/определенных ячеек в Excel Microsoft Excel, Руководство, Лайфхак, Видео, Длиннопост

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

Показать полностью 7 1
Отличная работа, все прочитано!