Excel Everyday

Всем привет! Очередная подборка уроков с нашего YouTube-канала. Там вы можете найти видео по работе в Power Query, подробные уроки по Excel, Word, Outlook и PowerPoint, а также длинные стримы на интересные темы.

Excel. Приемы. 3 способа подсчета итогов по условиям - https://youtu.be/fmIfWLyYQfU

Power Query. Как превратить перекрестную таблицу в плоскую - https://youtu.be/N2VzdYasSPQ

Excel. Как округлять время - https://youtu.be/bCLRaEJios4&t

Excel. Как вставить скан печати на каждый лист таблицы - https://youtu.be/tn-F1pXatn4

Power Point. Основы. Заметки к слайдам - https://youtu.be/ntMUzXTpF7E

Как верно заметил один из наших читателей, пользовательская функция из прошлого урока не срабатывает для чисел, у которых десятичная часть отделена запятой (что является стандартом русскоязычной локали). Это происходит потому, что в VBA требует точку в качестве разделителя целой и дробной части. Доработать код совсем просто. Достаточно добавить туда замену запятой на точку (функция Replace).

#УР4 #Макросы

В последнее время сразу несколько подписчиков обратились к нам с вопросом, как посчитать сумму, если она записана в одной ячейке в виде "5+10+15+20" и т.д. Проблема в том, что такая запись представляет собой текст. Обычные формулы при попытке провести математические операции с такой ячейкой будут либо выдавать ноль, либо ошибку.

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

#УР4 #Макросы

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

1. Шапка таблицы - из одной строки, все заголовки разные
2. В таблице нет полностью пустых строк и столбцов, объединенных ячеек
3. В таблице нет промежуточных итогов и подзаголовков
4. Таблица отделена от других данных пустой строкой и столбцом
5. Каждый столбец содержит данные одного типа
6. Плоские таблицы вместо перекрестных
7. Не плодите лишние таблицы
8. Форматирование - не средство фиксации важной информации
9. Разные по смыслу таблицы - на разных листах
10. Используйте "умные" таблицы

Подробнее про эти правила можно почитать в нашей статье: https://bit.ly/2EZ95aF

#УР1 #Оформление_таблиц

С помощью функций СЧЕТЕСЛИ и СРЗНАЧ можно легко подсчитать количество значений в диапазоне, которые превышают среднее значение. Главное - правильно записать условие сравнения: взять знак ">" в кавычки и не забыть символ склеивания текста - &.

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

#УР2 #Применение_встроенных_функций

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

#УР1 #Фильтрация_и_сортировка

Всем привет! Очередная подборка уроков с нашего YouTube-канала. Там вы можете найти видео по работе в Power Query, подробные уроки по Excel, Word, Outlook и PowerPoint, а также длинные стримы на интересные темы.

Power Query. Как подключить готовый запрос к другому файлу - https://youtu.be/Z79HgaP419k

Диаграммы Excel для визуализации KPI: Спидометр, Термометр, Круговая шкала - https://youtu.be/Y6duLTiEHBM

Excel. Как сделать сверку таблиц - https://youtu.be/4V75uvnZERY

Excel. Запрет на ввод дубликатов - https://youtu.be/SSrYl11krmw

Outlook. Правила. Перемещение писем в папку по теме - https://youtu.be/NEfHYTuZMeQ

Лишние пробелы в тексте - довольно частая проблема в Excel. Один лишний пробел в конце или начале строки (а может и посередине) легко приведет к неработоспособности формул (таких как СУММЕСЛИ или ВПР). Исправить проблему, как известно, можно с помощью функции СЖПРОБЕЛЫ.

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

#УР2 #Проверка_данных

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

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

#УР2 #Проверка_данных

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

Такая проверка может быть полезна при вводе каких-то артикулов, серийных номеров и т.д. Реализуется простейшими текстовыми формулами ДЛСТР и ЛЕВСИМВ.

#УР2 #Проверка_данных

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

Там есть специальная галочка, которая отвечает за возможность пользоваться маркером автозаполнения. Если она снята - маркер отключен. Если стоит - то всё работает, как обычно.

#Справка

Часто ли Вы используете в работе формулы с кучей вложенных функций ЕСЛИ? Возможно, стоит их заменить на что-то менее громоздкое. Например, на ВПР с приблизительным поиском.

Такой вариант использования функции отлично подойдет для задач вида:
- по сумме покупки найти скидку (от 500 до 1000 - 5%, от 1000 до 3000 - 10% и т.д.)
- по набранным баллам вывести результат (до 30 - плохо, 31-60 - норма и т.д.)
- по объему прожад посчитать премию и т.д.

Главное, помните - в справочнике надо указывать для каждого диапазона нижнюю границу. И эти границы ОБЯЗАТЕЛЬНО должны быть отсортированы по возрастанию.

#УР2 #Применение_встроенных_функций

Всем привет! Очередная подборка уроков с нашего YouTube-канала. Там вы можете найти видео по работе в Power Query, подробные уроки по Excel, Word, Outlook и PowerPoint, а также длинные стримы на интересные темы.

Power Query. Работа с шагами запроса - https://youtu.be/tLszr-bmoqQ

Excel. Диаграммы. Обратный порядок по оси - https://youtu.be/d1HIjMXkUN0

Excel. Как перенести стили из одного файла в другой - https://youtu.be/QaLoNTdYdpU

Excel. 3 способа скрыть содержимое ячеек при печати - https://youtu.be/PlO9XOjTsXg

Power Point. Основы. Правильная нумерация слайдов - https://youtu.be/FoMTQ8JpxYE

Прием для тех, кто только осваивает работу с формулами и функциями в Excel. Если написать название функции, а затем нажать сочетание клавиш Ctrl+Shift+A (A - латинская), то в ячейку будет вставлен "шаблон" функции со всеми аргументами.

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

#Горячие_клавиши

При распечатке больших таблиц, которые занимают несколько страниц, имеет смысл проставить номера этих страниц в колонтитулах. Благо, делается это в Excel очень просто. Предусмотрены специальные поля: "Номер страницы" и "Число страниц".

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

#УР1 #Печать_таблиц

Всем привет! Очередная подборка уроков с нашего YouTube-канала. Там вы можете найти видео по работе в Power Query, подробные уроки по Excel, Word, Outlook и PowerPoint, а также длинные стримы на интересные темы.

Excel. Сводные таблицы. Фиксация ширины столбцов - https://youtu.be/sTIooEZ-rHQ

Power Query. Быстрое удаление столбцов - https://youtu.be/Wz1mNevoITQ

Excel. Как выделить всю строку по условию - https://youtu.be/eAxsGqrcMF8

Excel. Консолидация данных из разных таблиц - https://youtu.be/pqqlNr_aQ34

Outlook. Организация информации. Определение размера папки - https://youtu.be/HcMziZJpflo

Иногда возникает потребность сделать на листе кнопку для перехода на другой лист. Реализуется это очень просто.

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

#УР1 #Работа_с_листами_книги

Умение мастерски пользоваться автофильтром - крайне полезный навык. Рассмотрим еще один хороший прием. Допустим, вы уже отобрали фильтром какие-то строки. Но теперь Вам надо найти еще одно значение и добавить к уже отобранным.

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

#УР1 #Фильтрация_и_сортировка

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

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

#УР1 #Фильтрация_и_сортировка

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

А можно воспользоваться простым трюком и выделить сразу все пустые ячейки, после чего правильно их удалить. Маленький, но эффективный и полезный прием.
#УР1 #Работа_с_листами_книги