VBA.Excel

VBA.Excel

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

Классный шорткат в Excel - Очередной повод отказаться от мышки!

Очередная отличная комбинация клавиш в Excel:

1398

Планировщик рабочих смен (и не только!) с динамическим календарём в Excel

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

Если у Вас есть идеи по оптимизации и расширению функционала этого календаря-планировщика – добро пожаловать в комментарии! Будет интересно почитать Ваши предложения 😊

420

Создаём динамический календарь в Excel с помощью формул и условного форматирования

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


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

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

300

Опять прописал всё ЗАГЛАВНЫМИ БУКВАМИ? Не надо исправлять вручную!

Абсолютно точно уверен, Вам это знакомо: печатаешь текст, поднимаешь глаза на экран и понимаешь, что всё прописано заглавными буквами. Всё стираешь, перепечатываешь текст заново… Больше так не делайте!


В этом посте хочу поделиться очень простой, крайне полезной, и далеко не всем известной горячей клавишей для Word и PowerPoint – Shift+F3.


В описанной ранее ситуации просто выберите текст зажмите шифт и затем нажмите F3:

Опять прописал всё ЗАГЛАВНЫМИ БУКВАМИ? Не надо исправлять вручную! Microsoft office, Microsoft Word, Горячие клавиши, Гифка, Видео, Длиннопост

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


При циклированном применении этой комбинации клавиш текст будет постоянно менять регистр в трёх вариантах - все прописные, все строчные, все прописные первые буквы:

Опять прописал всё ЗАГЛАВНЫМИ БУКВАМИ? Не надо исправлять вручную! Microsoft office, Microsoft Word, Горячие клавиши, Гифка, Видео, Длиннопост

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

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

Как удалить фон картинки в Excel / Word / PowerPoint – Просто!

Если вдруг нужно вырезать фон картинки, а под рукой нет Photoshop-а – не беда! Ведь эту задачу можно легко решить и непосредственно в Excel, Word-е, или же в PowerPoint-е.


Возьмем в качестве примера вот эту картинку:

Как удалить фон картинки в Excel / Word / PowerPoint – Просто! Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Лайфхак, Видео, Длиннопост

Вырежем ей фон в Excel, и укажем вырезанным символом руки на показатель выручки в Берлине за июль вот в этом небольшом отчете:

Как удалить фон картинки в Excel / Word / PowerPoint – Просто! Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Лайфхак, Видео, Длиннопост

Итак, переходим по пути команд: ВСТАВКА -> Рисунки -> Это устройство

Как удалить фон картинки в Excel / Word / PowerPoint – Просто! Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Лайфхак, Видео, Длиннопост

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

Как удалить фон картинки в Excel / Word / PowerPoint – Просто! Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Лайфхак, Видео, Длиннопост

Вот и наша картинка:

Как удалить фон картинки в Excel / Word / PowerPoint – Просто! Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Лайфхак, Видео, Длиннопост

Теперь выбираем её и во вкладке «Формат рисунка» щелкаем по кнопке «Удалить фон»:

Как удалить фон картинки в Excel / Word / PowerPoint – Просто! Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Лайфхак, Видео, Длиннопост

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

Как удалить фон картинки в Excel / Word / PowerPoint – Просто! Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Лайфхак, Видео, Длиннопост

Оставив лишь требуемое, нажимаем на «Сохранить изменения» и получаем желаемый результат:

Как удалить фон картинки в Excel / Word / PowerPoint – Просто! Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Лайфхак, Видео, Длиннопост

Ну и размещаем, как хотели, вырезанный указатель:

Как удалить фон картинки в Excel / Word / PowerPoint – Просто! Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Лайфхак, Видео, Длиннопост

Вот и всё! Через правый щелчок вырезанную картинку можно легко сохранить в формате PNG (в этом формате прозрачные области картинки остаются таковыми и не заполняются белым цветом).


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

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

Забудьте об объединении ячеек, ведь намного лучше поступать вот так!

Точно уверен, что Вам часто приходилось понервничать с объединёнными ячейками!


Возьмём в качестве примера вот такую таблицу:

Забудьте об объединении ячеек, ведь намного лучше поступать вот так! Microsoft Excel, Лайфхак, Гифка, Видео, Длиннопост

Выглядит, вроде, неплохо, вот только большим, на первый взгляд невидимым, минусом является то, что ячейки в диапазонах B2:E2 и B8:E8 объединены!

Забудьте об объединении ячеек, ведь намного лучше поступать вот так! Microsoft Excel, Лайфхак, Гифка, Видео, Длиннопост

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

Забудьте об объединении ячеек, ведь намного лучше поступать вот так! Microsoft Excel, Лайфхак, Гифка, Видео, Длиннопост

Наверняка Вы уже и сами пользовались подобным объединением ячеек – и я Вам хочу дать совет – постарайтесь больше этот инструмент не использовать, так как он может быть источником самых разных проблем - начиная от проблем с шорткатами, вплоть до сложностей при написании кода в VBA.


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


Для этого сперва отменяю объединение ячеек:

Забудьте об объединении ячеек, ведь намного лучше поступать вот так! Microsoft Excel, Лайфхак, Гифка, Видео, Длиннопост

После этого выбираю все требуемые ячейки (я выбрал ячейки сразу как в строке 2, так и 8):

Забудьте об объединении ячеек, ведь намного лучше поступать вот так! Microsoft Excel, Лайфхак, Гифка, Видео, Длиннопост

Теперь щелкаем по вот этому символу:

Забудьте об объединении ячеек, ведь намного лучше поступать вот так! Microsoft Excel, Лайфхак, Гифка, Видео, Длиннопост

И в открывшемся окне в пункте «по горизонтали» выбираем настройку «по центру выделения».

Забудьте об объединении ячеек, ведь намного лучше поступать вот так! Microsoft Excel, Лайфхак, Гифка, Видео, Длиннопост

Нажимаем на ОК и всё готово (на картинке ниже показано, что объединение ячеек не использовано)!

Забудьте об объединении ячеек, ведь намного лучше поступать вот так! Microsoft Excel, Лайфхак, Гифка, Видео, Длиннопост

Теперь все выглядит как нам нужно было, и при этом и в функциональном плане всё работает как должно – вот могу спокойно выбирать нужные ячейки:

Забудьте об объединении ячеек, ведь намного лучше поступать вот так! Microsoft Excel, Лайфхак, Гифка, Видео, Длиннопост

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

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

Быстрые формы ввода данных в Excel за пару секунд – без VBA!

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

Быстрые формы ввода данных в Excel за пару секунд – без VBA! Microsoft Excel, Данные, Видео, Длиннопост

То всё достаточно просто – заполнение можно производить построчно, пошагово дополняя по строчке в таблицу:

Быстрые формы ввода данных в Excel за пару секунд – без VBA! Microsoft Excel, Данные, Видео, Длиннопост

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

Быстрые формы ввода данных в Excel за пару секунд – без VBA! Microsoft Excel, Данные, Видео, Длиннопост

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


Одним из возможных решений подобной проблемы является использование форм для ввода данных. При это есть два варианта:

1. Пользовательские формы VBA

2. Быстрые формы ввода данных Excel


При первом варианте нужно уметь программировать в VBA, но и результат намного более качественный и индивидуализированный – фактически, можно создать всё что угодно.


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


Разберем, как вызывать это самые быстрые формы ввода данных Excel. Для этого сперва нужно требуемую таблицу записи перевести в формат умной таблицы. Для этого выбираем её (1) используем комбинацию клавиш CTRL+T (2) и подтверждаем перевод в формат умной таблицы (3):

Быстрые формы ввода данных в Excel за пару секунд – без VBA! Microsoft Excel, Данные, Видео, Длиннопост

В результате получаем вот такую умную таблицу:

Быстрые формы ввода данных в Excel за пару секунд – без VBA! Microsoft Excel, Данные, Видео, Длиннопост

Вот для её заполнения теперь и можно вызвать быструю форму, однако, в последних версиях Excel кнопка вызова быстрых форм была убрана с ленты команд. Поэтому добавим её на панель быстрого доступа. Для этого нажимаем по вот этой стрелке (1) и затем по опции «Другие команды» (2):

Быстрые формы ввода данных в Excel за пару секунд – без VBA! Microsoft Excel, Данные, Видео, Длиннопост

В следующем окне в выпадающем списке слева выбираем «Команды не на ленте» (1) и почти в самом конце списка ниже дважды щелкаем по «Форма» (2). После её добавления в список справа нажимаем на ОК (3).

Быстрые формы ввода данных в Excel за пару секунд – без VBA! Microsoft Excel, Данные, Видео, Длиннопост

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

Быстрые формы ввода данных в Excel за пару секунд – без VBA! Microsoft Excel, Данные, Видео, Длиннопост

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


Тем не менее, конечно, видно, что эта автоматическая форма ввода данных больше в Excel-е не развивается (поэтому она была убрана с ленты) и функционал у нее крайне базовый. Лично я бы поэтому ей пользоваться не стал бы и всё-таки решал бы свои задачи с помощью пользовательских форм VBA. Тем не менее, для общеэрудиционных целей и об этом инструменте в Excel следует знать.


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

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

Генератор заумных речей в Excel

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

Генератор заумных речей в Excel Microsoft Excel, Функция, Видео, Длиннопост

Идея заключается в том, что если рандомно соединять по одному элементу из каждого столбца в порядке I -> II-> III -> IV, то каждый раз будут выходить многозначительно звучащие предложения.


А чтобы не соединять эти элементы самим в уме, можно прописать это всё в Excel (заодно в очередной раз поупражнявшись в работе с функциями СЛУЧМЕЖДУ, ИНДЕКС и ОБЪЕДИНИТЬ).


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

Генератор заумных речей в Excel Microsoft Excel, Функция, Видео, Длиннопост

Следующим шагом подтянем в отдельную строку выбранные функцией СЛУЧМЕЖДУ части предложения. Воспользуемся для этого функцией ИНДЕКС:

Генератор заумных речей в Excel Microsoft Excel, Функция, Видео, Длиннопост

Ну и теперь объединим отдельные части в одно предложение с помощью функции ОБЪЕДИНИТЬ:

Генератор заумных речей в Excel Microsoft Excel, Функция, Видео, Длиннопост

Вот и всё! Теперь нажимая на F9 можно производить перерасчёт в рабочей книге (Excel будет выдавать другие рандомные числа функцией СЛУЧМЕЖДУ) и в итоге будут выводиться всё новые и новые случайным образом подобранные предложения из отдельных элементов.


Увидеть, как работает этот перерасчёт с помощью клавиши F9 можно вот в этом видео:

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

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

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


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

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

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

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

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

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

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

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


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

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

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

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

Начальник сказал очистить таблицу от тысячи пустых строк за час? Выполняем за 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
Отличная работа, все прочитано!