Excel Everyday

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

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

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

Реализуется такой вариант функцией ВПР или связкой ИНДЕКС+ПОИСКПОЗ. Одно значение, которое служит ключом (является уникальным в справочнике), мы выбираем из списка, а все остальные данные просто подтягиваем в таблицу из справочника по выбранному ключу.

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

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

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

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

Сегодня показываем небольшую формулу, которая позволяет подсчитать сумму цифр какого-то целого числа. Например, для числа 549 этой суммой будет 5+4+9 = 18.

В основе формулы - функция ПСТР. С ее помощью число разбирается на отдельные цифры, которые потом переводятся из текста в число (с помощью двойного отрицания). В конце все полученные значения суммируются в итоговый результат.

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

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

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

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

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

Тогда задача сводится к извлечению целой и дробной части по отдельности. Первую можно достать функцией ЦЕЛОЕ, а вторую можно выразить как остаток от деления числа на единицу (остатком в таком случае будет дробная часть). Формулы очень простые и короткие.

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

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

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

#УР3 #Диаграммы

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

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

#УР2 #Условное_форматирование

Статус выполнения задачи или наличия чего-то очень удобно отображать визуально через простановку галочек. Чтобы не возиться с чекбоксами в Excel, можно использовать различные альтренативы. Одна из них - условное форматирование.

Ставим в ячейки 0 для крестика и 1 для галочки. Добавляем соответствующее правило с красивыми значками и отключаем отображение чисел в ячейках. Останется только на всякий случай настроить проверку данных, чтобы не ввести что-то лишнее, на чём условное форматирование может не сработать.

#УР2 #Условное_форматирование

В сводной таблице есть возможность вручную группировать значения в строках или столбцах. Достаточно выделить нужные значения (используя CTRL или SHIFT), кликнуть на них правой кнопкой мыши и выбрать команду "Группировать". Останется только поменять название созданной группы.

Для многоуровневых группировок следует соблюдать порядок создания. Сначала создаюстя группы самого глубокого уровня вложенности. Затем - родительские для них и т.д. Использование группировок позволяет мгновенно увидеть в сводной таблице промежуточные итоги по созданным группам.

#УР2 #Сводные_таблицы

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

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

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

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

Добавляем дополнительный столбец (если меняем сортировку строк) или дополнительную строку (если меняем сортировку столбцов). Заполняем их числами от 1 по возрастанию. А затем просто сортируем всю таблицу по добавленной строке/столбцу в порядке убывания. Для сортировке по строке, правда, придётся использовать команду "Настраиваемая сортировка", но ничего сложного в этом нет.

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

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

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

Кроме чисел это работет и со списками (названия месяцев и дней недели и собственные пользовательские списки).

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

Интересная задача: заполнить диапазон случайным образом значениями в заданной пропорции. Например, в соотношении 70% на 30%. Если вам не нужно исключительно точное решение, то можно попытаться воспользоваться стандартными функциями Excel.

В частности, функция СЛЧИС, которая генерирует десятичное число от 0 до 1, может помочь. В сочетании с ЕСЛИ она позволит заполнить диапазон значениями в соотношении, примерно равном требуемому. Чем больше диапазон - тем точнее пропорция.

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

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

В ее основе будет лежать все та же функция СЧЕТЕСЛИ, а так как мы вводим формулу в окно проверки данных, то нам даже не потребуется "массивный" ввод. Просто пишем формулу и жмём ОК. Ну и помните, что проверка данных - это атрибут ячейки. При копировании в контролируемый диапазон другой ячейки - проверка слетает.

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

Проверка данных позволяет указывать формулы в качестве критерия. При вводе значения в такую ячейку после нажатия на Enter будет вычислена формула, и если она вернет ИСТИНА - ввод будет произведен. Если ЛОЖЬ - ввод будет запрещен.

Формулой можно, например, контролировать ввод конкретного символа. Если надо запретить вводить пробел, или цифру 0, то простая функция СЧЁТЕСЛИ позволит осуществить контроль вводимых данных. Для наглядности можно указать подсказку при возникновении ошибки.

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

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

Придется писать формулу массива. Один из вариантов - использование функции СОВПАД. Она умеет сравнивать два значения, принимая во внимание различие строчных и прописных букв. Ну а работать она будет, разумеется, в связке с ИНДЕКС и ПОИСКПОЗ, как это обычно и бывает в подобных задачах.

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

При работе со справочником и функциями ВПР или ИНДЕКС+ПОИСКПОЗ пользователи часто применяют функцию ЕСЛИОШИБКА, чтобы вместо ошибки НД выводить, например, текст "Нет в справочнике". У этого подхода есть одна проблема.

ЕСЛИОШИБКА обрабатывает любую ошибку. Если значения действительно нет в справочнике, то всё в порядке. Мы получаем нужный текст-заменитель. Но если в справочнике значение есть, а вот результат, который надо вернуть, является ошибкой - то текст "Нет в справочнике" будет не корректен. Ведь ошибка не в этом. В таких случая лучше использовать специальные функции для обработки именно ошибки НД. Используйте ЕНД или ЕСНД (если у вас Excel 2013 или новее).

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

Всем привет!
Недавно мы провели часовой онлайн-вебинар по Excel. На примере небольшого кейса разобрали такие темы, как:
- Подготовка данных для дальнейшего анализа
- Работа со справочниками и вспомогательными таблицами
- Работа со сводными таблицами
- Построение интерактивных динамических диаграмм
- и многое другое

Посмотреть запись вебинара Вы можете вот здесь:
https://youtu.be/gt5C2pJLpQ8

Делитесь с друзьями, ставьте лайки, подписывайтесь на наш YouTube-канал!

Вопросы по вебинару или просто по Excel, как всегда, можете задавать в нашего бота: @ExEvFeedbackBot

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

Если нужно сделать так, чтобы каждый новый документ создавался с заданными настройками, то повторите следующие действия:
1) Создайте файл с нужным оформленим
2) Проверьте, по какому пути хранится папка автозапуска пользователя XLSTART
3) Сохраните созданный файл в эту папку в формате Шаблон с именем Книга (для английских версий имя должно быть Book).

Теперь при запуске Excel, использовании команды Создать на панели быстрого доступа или сочетания CTRL+N будет создана книга из сохраненного шаблона.

Исключение - создание книги через Файл - Создать. В таком случае файл будет иметь стандартные настройки. Ну и разумеется, чтобы вернуть все на место достаточно просто удалить шаблон из XLSTART.

#Справка