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

Есть возможность отключить отображение нулевых значений в настройках Excel.

Для этого выполните следующие действия:

1️⃣ На панели инструментов кликните по пункту «Файл»
2️⃣ В меню слева кликните по пункту «Параметры»
3️⃣ В окне «Параметры Excel» перейдите в раздел «Дополнительно»
4️⃣ Прокрутите меню до раздела «Параметры отображения листа»
5️⃣ Снимите галочку с пункта «Показывать нули в ячейках, которые содержат нулевые значения»
6️⃣ Нажмите «ОК»

✅ Готово!

Ваша таблица теперь не содержит нули!

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

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

Рассмотрим, как создать выпадающий список из перечня месяцев.

1️⃣ Создадим список месяцев, которые мы хотим поместить в выпадающий список
2️⃣ Выделим левой клавишей мыши диапазон ячеек, в котором мы хотим создать выпадающие списки
3️⃣ Перейдем на вкладку «Данные» на панели инструментов и кликнем по пункту «Проверка данных»
4️⃣ В окне «Проверка вводимых значений» выберем в поле «Тип данных» пункт «Список»
5️⃣ В поле «Источник» укажем диапазон ячеек с перечнем месяцев, который мы подготовили на первом этапе
6️⃣ Кликнем «ОК»

✅ Готово!

В ячейках столбца «Месяц» созданы выпадающие списки.

При сортировке названий месяцев в Excel есть свои особенности. Если к списку с названием месяцев применить стандартную сортировку от А до Я, то первым в списке месяцев будет Август, а крайним Январь.

Для сортировки месяцев в порядке с Января по Декабрь выполните следующие действия:

1️⃣ Выделите левой клавишей мыши диапазон ячеек с месяцами
2️⃣ На вкладке Данные на панели инструментов кликните по пункту Сортировка
3️⃣ В окне Сортировка кликните по выпадающему списку Порядок
4️⃣ Выберите пункт Настраиваемый список
5️⃣ Нажмите ОК
6️⃣ В окне Списки выберите подходящую настройку по сортировке месяцев
7️⃣ Нажмите ОК

✅ Готово!

Список с месяцами отсортирован в порядке с Января по Декабрь.

Многие пользователи Excel периодически сталкиваются с такой проблемой: создаём формулу, протягиваем её на соседние ячейки и... ничего не происходит. В соседние ячейки копируется одно и то же значение. 🧐

В чем проблема? 💁‍♂️

Чаще всего это связано с тем, что сбросились параметры вычислений в Excel на «ручной режим».

Для решения проблемы:

1️⃣ Перейдите на вкладку «Формулы» на панели инструментов
2️⃣ Кликните по пункту «Параметры вычислений»
3️⃣ Выберите пункт «Автоматически»

✅ Готово!

Формулы в вашем Excel файле вновь будут автоматически обновляться!

Заполняя большие списки с данными в Excel важна точность! Ошибки при вводе недопустимы!

Для точного заполнения данных вам поможет быстрый выпадающий список в Excel.

Для активации быстрого выпадающего списка:

1️⃣ Выделите левой клавишей мыши ячейку Excel
2️⃣ Нажмите сочетание клавиш Alt и стрелку вниз
3️⃣ Выберите подходящий элемент из списка

Особенности работы быстрого выпадающего списка в Excel:

1️⃣ Позволяет выбирать только данные находящиеся непосредственно над ячейкой
2️⃣ Над ячейкой не должно быть пробелов
3️⃣ Нет возможности выбирать элементы находящиеся справа/слева/снизу ячейки

Многие при работе со сводными таблицами в Excel сталкиваются с ошибками и трудностями в работе.

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

⛔️ Плохой список данных для сводной таблицы:

1️⃣ Включает объединенные ячейки
2️⃣ Отсутствуют заголовки у столбцов
3️⃣ Включают строки с итогами и калькуляциями
4️⃣ Имеет пустые ячейки и ошибки

✅ Правильный список данных для сводной таблицы:

1️⃣ Данные представлены по строкам для каждого параметра
2️⃣ Нет излишнего форматирования
3️⃣ Включает только значения
4️⃣ Имеет заголовки
5️⃣ Нет доп. заголовков, строк с итогами

Соблюдайте эти простые правила для подготовки корректных списков данных ваших сводных таблиц.

Поделитесь, что из описанного выше для вас было новым? 👇

В Excel возможно добавление фонового изображения на лист.

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

Чтобы добавить фоновое изображение на лист Excel выполните следующие действия:

1️⃣ На панели инструментов кликните по пункту "Разметка страницы"
2️⃣ Кликните левой клавишей мыши по пункту "Подложка"
3️⃣ Выберите изображения с компьютера или воспользуйтесь поиском
4️⃣ Нажмите "ОК"

✅ Готово!

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

____
Советую обратить внимание на канал Обучайся Excel.
Внутри вы найдете материалы об оформлении, сводных таблицах и интересные решения рабочих задач.

В-общем присоединяйтесь к @exstudy.

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

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

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

1️⃣ Выделите левой клавишей мыши диапазон ячеек с ФИО
2️⃣ Нажмите сочетание клавиш CTRL + F для вызова меню "Найти и заменить"
3️⃣ Перейдите во вкладку "Заменить"
4️⃣ В поле "Найти" введите значение *.
5️⃣ Поле "Заменить на" оставьте пустым
6️⃣ Нажмите кнопку "Заменить все"

✅ Готово!

Наш список содержит только ФИО.

Подстановочный знак * обозначает любое количество символов.

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

Поделитесь, пожалуйста, в комментариях был ли для вас полезен этот #эксельхак? 👇

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

Для поиска последнего числового значения в строке нам поможет функция ПРОСМОТР.

Функция ПРОСМОТР ищет слева направо искомое значение в заданном диапазоне ячеек.

Если функция не найдет искомое значение, то вернет нам значение из крайней ячейки диапазона.

Задайте функции ПРОСМОТР в качестве искомого значения ОЧЕНЬ большое число, которого нет в диапазоне просматриваемых ячеек. Например, 9999999.

Формула в нашем примере для ячейки F3 будет выглядеть так:

=ПРОСМОТР(9999999;B3:E3)

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

Для быстрого сброса формата ячеек в Excel выполните несколько простых действий:

1️⃣ Выделите левой клавишей мыши диапазон ячеек для очистки формата
2️⃣ На вкладке "Главная" кликните по пункту "Очистить"
3️⃣ В выпадающем меню выберите "Очистить форматы"

✅ Готово! Формат таблицы сброшен!

Сократите количество кликов для сброса формата ячеек:

1️⃣ Кликните правой клавишей мыши по пункту "Очистить форматы"
2️⃣ Выберите "Добавить на панель быстрого доступа"

✅ Кнопка сброса формата теперь на панели быстрого доступа.
Сбрасывайте форматы ячеек в один клик!

Очень простой, но важный совет!

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

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

Для этого я рекомендую использовать функцию ДАТАЗНАЧ (DATEVALUE на англ.). В качестве аргумента укажите ссылку на ячейку с текстовой датой.

Если в ваших таблицах множество БОЛЬШИХ чисел, то для наглядности их можно привести в формат "тысяч".

Например, для преобразования числа 137 500 р. в 137,5 тыс. руб выполните следующие действия:

1️⃣ Выделите левой клавишей мыши ячейку для преобразования
2️⃣ Нажмите сочетание клавиш "CTRL + 1" для вызова меню "Формат ячеек"
3️⃣ На вкладке "Число" перейдите в пункт "Все форматы"
4️⃣ В поле Тип пропишите формат:

0,0 " тыс. руб."

5️⃣ Нажмите "ОК"

✅ Готово!

P.S. Если вы хотите применить формат в миллионах рублей, то добавьте дополнительный пробел после числа ноль и замените "тыс." на "млн."

Формат для миллионов рублей выглядит так:

0,0 " млн. руб."

А вы знаете что такое "Срезы" в Excel? 🧐

"Срез" – это кнопки для удобной фильтрации таблиц в Excel.

Применить "Срезы" вы можете только к данным, отформатированным как таблица, а также к сводным таблицам в Excel.

Добавьте "Срезы" к списку с данными выполнив следующие действия:

1️⃣ Кликните левой клавишей мыши по списку с данными
2️⃣ На вкладке "Главная" на панели инструментов кликните по пункту "Форматировать как таблицу"
3️⃣ В окне "Форматирование таблицы" поставьте галочку если ваш список содержит заголовки и нажмите "ОК"

✅ Готово!

Данные отформатированы как таблица. Теперь, мы можем активировать "Срезы".

4️⃣ Кликните левой клавишей мыши по любой ячейке таблицы
5️⃣ Перейдите на вкладку "Конструктор таблиц" на панели инструментов
6️⃣ Кликните по пункту "Вставить срез"
7️⃣ В меню "Вставка срезов" отметьте те столбцы вашей таблицы по которым вы хотите создать "Срезы"
8️⃣ Нажмите "ОК"

✅ Готово!

Теперь, фильтровать данные в таблице быстро, наглядно и удобно.

Очень часто получаю вопрос: "Почему у меня и строки и столбцы отмечены цифрами на листе Excel?"

Так происходит когда у вас активирован стиль ссылок R1C1.

В Excel два стиля ссылок А1 и R1C1.

A1 - классический стиль, где буква это номер столбца листа Excel, а число – номер строки.

R1C1 - стиль ссылок Excel где номера строки (R) и столбца (C) указываются числами и указывают координаты относительно активной ячейки.

Если в ячейку из первого столбца и первой строки ввести ссылку на ячейку R[1]C[2] то эта ссылка означает, что она сошлется на ячейку, которая находится на 1 строку ниже активной ячейки и на два столбца правей.

✅ Как отключить стиль ссылки R1C1

1️⃣ Нажмите "Файл" => "Параметры"
2️⃣ В окне "Параметры Excel" на вкладке "Формулы" уберите галочку "Стиль ссылок R1C1"
3️⃣ Нажмите "ОК"

✅ Готово!

По умолчанию в сводных таблицах нет возможности посчитать уникальные значения.

Но, есть #эксельхак 😉

Если у вас версия Excel 2013 и выше, то для подсчета уникальных значений выполните следующие действия:

1️⃣ Кликните левой клавишей мыши по любой ячейке списка с данными
2️⃣ Перейдите на вкладку "Вставка" и кликните по пункту "Сводная таблица"
3️⃣ В окне "Создание сводной таблицы" поставьте галочку в пункте "Добавить эти данные в модель данных"
4️⃣ Нажмите "ОК"
5️⃣ Поместите в область "Значения" сводной таблицы элемент по которому вы хотите получить расчет уникальных значений
6️⃣ Кликните по стрелочке элемента в области "Значения"
7️⃣ Нажмите "Параметры полей значений"
8️⃣ В окне "Параметры поля значений" на вкладке "Операция" выберите пункт "Число разных элементов"
9️⃣ Нажмите "ОК"

✅ Готово!

В вашей таблице будут отражено количество уникальных значений.