Быстрый, но полезный #эксельхак о том, как быстро фильтровать данные сводной таблицы Excel.

Уверен, вы сталкивались с такой ситуацией когда в построенной сводной таблице нужно убрать какие то данные.

Например, исключить из списка каких-то сотрудников, отделы, продукты и так далее.

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

У меня есть для вас более быстрый способ! 😉

❗ Используйте сочетание клавиш CTRL и -

Просто выделите левой клавишей мыши тот элемент, который вы хотите вынести под фильтр сводной таблицы и нажмите сочетание клавиш CTRL и -

✅ Готово!

Этот способ подойдет как для строк, так и для столбцов.

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

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

Определить число вставленное как текст просто:

Текстовые значения по умолчанию упорядочены влево 👈

Числовые значения упорядочены вправо 👉

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

Числа как текст можно легко преобразовать в настоящие числа. Для этого:

1️⃣В любой ячейке на листе укажем число 1
2️⃣Скопируем эту ячейку нажав сочетание клавиш CTRL+C
3️⃣Выделим диапазон ячеек который хотим преобразовать в числовой формат
4️⃣Кликнем правой клавишей мыши по выделенному диапазону
5️⃣В контекстном меню выберем пункт «Специальная вставка»
6️⃣В окне «Специальная вставка» поставим галочку напротив пункта «Умножить» и нажмем ОК

✅Готово

Что мы сделали? Мы просто умножили весь диапазон ячеек на 1 и автоматически присвоили им числовой формат.

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

Что в этом случае делать?

Мы можем скрыть ошибки на листе при печати!

Сделать это просто:

1️⃣ Щелкните Файл => Печать
2️⃣ Кликните по пункту Параметры страницы
3️⃣ В новом окне перейдите на вкладку Лист
4️⃣ В разделе Ошибки ячеек как выберите пункт <нет>
5️⃣ Щелкните ОК

✅ Готово

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

Но я вам рекомендую правильно составлять формулы и не допускать ошибок.

При выгрузке данных из 1С и других систем мои ученики часто сталкиваются с тем, что числа отделены пробелом, а применение функции СЖПРОБЕЛЫ() не справляется с удалением лишних пробелов.

Как убрать лишние пробелы в таких числах?

1️⃣ Дважды кликните по ячейке левой клавишей мыши
2️⃣ Выделите левой клавишей мыши символ пробела
3️⃣ Нажмите сочетание клавиш CTRL+C для копирования
4️⃣ Выделите диапазон ячеек с числами для преобразования
5️⃣ Нажмите сочетание клавиш CTRL + H
6️⃣ В поле "Найти" вставьте скопированный пробел с помощью клавиш CTRL + V
7️⃣ Поле "Заменить на" оставьте пустым
8️⃣️ Щелкните "Заменить все" и "Закрыть"

✅ Готово

Числа с пробелами из 1С успешно преобразованы в обычные числа.

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

Но, если в нашей таблице нет 100% значений, то ячейка с максимальным значением в 69% будет полностью залита.

Я хочу, чтобы ячейка, например, со значением 50% была закрашена на половину, а с числом 75% на три четверти.

Для этого нужно скорректировать правило условного форматирования:

1️⃣ На вкладке "Главная" щелкните по пункту "Условное форматирование", затем по "Управление правилами"
2️⃣ В диспетчере правил условного форматирования кликните по правилу, щелкните по "Изменить правило"
3️⃣ В окне "Изменение правила форматирования" в поле "Тип" для максимального значения выберите "Число"
4️⃣ В поле "Значение" для максимального значения укажите 100% или 1
5️⃣ Щелкните "ОК"
6️⃣ В окне диспетчера правил нажмите "Применить", а затем "ОК"
✅ ГОТОВО

Для сортировки таблицы в случайном порядке я рекомендую использовать функцию СЛЧИС (англ. RAND).

СЛЧИС это такая функция, которая выдает случайное число от 0 до 1.

Итак:

1️⃣В соседнем с нашей таблицей столбце  введем формулу с функцией СЛЧИС и протянем ее на все ячейки находящиеся рядом с таблицей
2️⃣Сохраним значения в ячейках с функцией СЛЧИС как значения скопировав и вставив как значения
3️⃣Активируем фильтр таблицы с помощью клавиш CTRL + SHIFT + L
4️⃣Отсортируем таблицу по столбцу со случайными числами.
5️⃣Удалим вспомогательный столбец.

✅Готово, наша таблица отсортирована в случайном порядке.

В Excel вы можете поменять цветовую палитру интерфейса. Например, применить черную тему.

Для этого:

1️⃣ Щелкните по вкладке Файл => Параметры
2️⃣ В окне с параметрами перейдите на вкладку Общие
3️⃣ В группе Личная настройка Microsoft Office в поле Тема Office выберите подходящий стиль оформления:

▪️чёрная
▪️темно-серая
▪️белая
▪️разноцветная

Выбранная тема будет распространяться на все продукты Microsoft Office (Outlook, Word, Power Point и т.д.

Темы могут отличаться от версии к версии в Excel.

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

1️⃣ Выделите диапазон ячеек для преобразования
2️⃣ На вкладке "Данные" щелкните по кнопке "Текст по столбцам"
3️⃣ В окне "Мастера" просто нажмите "Готово"

✅ Готово

В Excel есть особенности по отображению количества часов в ячейке, если значение равно более чем 24 часа.

Excel округляет значения времени при достижении
24 часов и отображает фактическое количество часов минус 24 часа.

Если дней несколько, то отображаются только часы исключая дни, умноженные на 24 часа.

Как избежать это? Как сделать так, чтобы в ячейке отображалось фактическое количество часов?

Нужно применить правильный формат к ячейке:

1️⃣Нажмите сочетание клавиш CTRL + 1 для вызова окна Формат ячеек
2️⃣Перейдите на вкладку Время и выберите формат 37:30:55
3️⃣Если вы хотите убрать значение секунд, то снова вызовите окно Формат ячеек
4️⃣Перейдите на вкладку Все форматы и удалите вручную значения секунд сс и двоеточие :
5️⃣Нажмите ОК

✅Готово. Теперь ваша ячейка отображает фактическое количество часов 👌

Сегодня я хочу поделиться с вами двумя способами фильтрации дат в Excel без привязки к году.

Представим, что у нас есть таблица со списком сотрудников и их датами рождений.

Я хочу отфильтровать таблицу и оставить только тех сотрудников у которых день рождения в Сентябре.

1️⃣ Способ №1

▪️ Активируем фильтрацию таблицы с помощью клавиш "CTRL + SHIFT + L"
▪️ Щелкнем по иконке фильтрации внутри ячейки
▪️ Перейдем в пункт "Фильтры по дате", а затем выберем "Все даты за период" и щелкнем по нужному нам названию месяца.

✅ Готово. Список успешно отфильтрован.

2️⃣ Способ №2

▪️ Мы также щелкнем по иконке фильтрации внутри ячейки
▪️ В поисковой строке фильтра напишем название месяца или первые буквы названия и щелкнем "ОК"

✅ Готово, в нашей таблице остались только те сотрудники день рождения у которых в Сентябре.

А вы знали, что к формуле в Excel можно добавить комментарий?

Не примечание, а комментарий!

Сделать это просто 👇

1️⃣Добавим к формуле в ячейке С3 функцию Ч() (англ. N())
2️⃣В кавычках пропишем комментарий
”минус 30% комиссия партнеров”.

=B2-(B2*0,3)+Ч("минус 30% комиссия партнеров")

📌Функция Ч() – преобразует значения в числа. Если функции задать текстовое значение – она воспримет его как ноль и никак не повлияет на результат работы формулы.  

✅Комментарий к формуле добавлен

А вас раздражают зеленые треугольники в углу ячеек Excel с формулами? 🧐

Эти треугольники - не ошибка!

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

Вы можете отключить уведомления:

1️⃣ Щелкните "Файл" 👉 "Параметры"
2️⃣ На вкладке "Формулы" уберите галочку с пункта "Формулы не охватывающие смежные ячейки"
✅ Готово