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

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

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

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

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

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

#Справка

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

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

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

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

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

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

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

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

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

Всем привет! Очередная подборка уроков с нашего 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 можно не только строки, но и столбцы. Иногда это бывает очень полезно. Правда, настройка такой сортировки находится довольно глубоко. Придется открыть окно "Сортировка", а в нем отыскать кнопку "Параметры" и установить соответствующий переключатель. В остальном всё делается по тем же принципам, что и при сортировке строк таблицы.

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

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

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

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

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

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

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

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

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

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

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

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

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

Всем привет! Очередная подборка уроков с нашего 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

Ранжировать строки в Excel приходится довольно часто. Обычно хватает встроенных функций РАНГ. Но у них есть один существенный недостаток. При одинаковых значениях эти функции либо выводят средний ранг (РАНГ.СР), либо присваивают значениям один и тот же ранг, а следующее по величине идет через пропуск нескольких номеров (РАНГ.РВ).

Если нужно, чтобы нумерация была без пропусков, строго от 1 до n, то придется дополнить функцию РАНГ небольшим побочным вычислением.

#УР2 #Примеры_формул

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

В самом деле, чтобы высчитать ранг для самого большого значения в группе, достаточно посчитать сколько есть в таблице строк с теми же условиями (той же группой), но равных или больших по ранжируемому параметру. Очевидно, что для самого большого значения такая строка будет 1 (это и есть ранг - первый). Для второго по величине таких строк будет уже две (позиция №2) и так далее.

P.S. Если в таблице будут полностью дублирующиеся строки (одинаковы и группы, и ранжируемая величина), то формулу надо будет чуть подправить. Покажем в следующем уроке.

#УР2 #Примеры_формул

Объединением двух предыдущих приемов является формула, которая умеет подсчитывать ранг внутри групп, да еще и без пропусков значений. В основе - всё та же универсальная СЧЕТЕСЛИМН. Главная фишка - хитрое закрепление диапазонов (первую ячейку делаем фиксированной, а вторую - свободной).

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

#УР2 #Примеры_формул

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

Он основан на хитром приеме. Заменяем каждый пробел в тексте на много подряд идущих пробелов. Слова "разъезжаются" далеко друг от друга. А потом отщипываем справа кусочек достаточной длины и избавляемся от лишних захваченных пробелов. Очень красивое решение.

#УР2 #Примеры_формул

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

Power Query. Как собрать несколько файлов в один - https://youtu.be/NVXdizXk99w

Автоматический отчёт. Как подружить Outlook и Excel с помощью макросов и Power Query - https://youtu.be/olF4ZuVub0M

Excel. Как подсчитать нарастающий итог в Excel - https://youtu.be/phipLdXO6_E

Excel. Как быстро переставить столбцы таблицы в нужном порядке - https://youtu.be/jra3Qyxnna8

Word. Основы. Добавление подложки - https://youtu.be/GYgJaTdQnms

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

Задача решается по-разному. Можно "разобрать" текст на части функциями ЛЕВСИМВ, ПСТР и ПРАВСИМВ, а потом склеить через дефисы и амперсанды. А можно поступить чуть хитрее и использовать функцию ЗАМЕНИТЬ, опустив третий аргумент.

#УР2 #Примеры_формул

В дополнение к предыдущему уроку. Если вам не нужно именно менять значение в ячейке (то есть физически вставлять в него дефисы), а нужно просто отобразить артикул в ячейке с разбивкой на группы по 3 цифры, то можно применить обычный числовой формат. Код совсем простой: "000-000-000". Только помните, что подобные форматы применимы исключительно для числовых значений.

#УР2 #Примеры_формул

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

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

#УР1 #Обработка_таблиц