55088
Уроки которые упростят жизнь и работу. Реклама: @Mr_Varlamov
В пользовательских правилах условного форматирования можно использовать формулы массива. Это даёт еще больше возможностей для создания кастомизированных правил.
Например, можно создать правило, которое будет выделять 3 строки с наибольшими продажами в таблице, но не по всем товарам, а только по тому, который выбран в выпадающем списке. При этом формула массива в окне правила условного форматирования не требует ввода тремя клавишами. Достаточно просто написать или вставить ее туда.
#УР2 #Условное_форматирование
Маркер автозаполнения замечательно работает и с датами. Помимо стандартного увеличения на единицу и возможности задать шаг, введя первые два значения, предлагается еще несколько опций.
Если потянуть за маркер автозаполнения правой кнопкой мыши, то откроется контекстное меню, в котором можно выбрать такие варианты как:
- заполнение только рабочими днями
- заполнение с шагом в 1 месяц
- заполнение с шагом в 1 год
#УР1 #Работа_с_листами_книги
По умолчанию при построении сводной таблицы и добавлении нескольких полей в область строк мы получаем вложенную иерархию. Часто это бывает довольно наглядно и удобно. Но если добавлено много полей, то чтение таблицы может оказаться затруднительным.
В таком случае лучше всего перестроить ее в классическую плоскую таблицу. Такая возможность предоставляется командами, расположенными на вкладке Конструктор.
#УР2 #Сводные_таблицы
📊 Освойте Excel с нуля — работайте быстрее и эффективнее
Специалисты из различных индустрий — маркетинга, финансов, аналитики и менеджмента используют Excel для анализа данных, автоматизации задач и повышения эффективности.
Если вы хотите научиться работать с таблицами, экономить время и оптимизировать рабочие задачи, приходите на бесплатный курс Нетологии: «Excel: простые шаги для оптимизации работы с данными».
💡 За 3 занятия вы:
— Освоите ключевые функции, диаграммы и формулы Excel.
— Поработаете со сводными таблицами и составите отчёт.
— Пройдёте весь путь анализа — от подготовки данных до представления результатов.
🎁 Регистрация уже открыта — стартуйте бесплатно и улучшите свои карьерные перспективы
Реклама. ООО "Нетология". ИНН 7726464125 Erid 2VSb5wpvJtQ
У сводных таблиц есть удобная опция фиксации ширины столбца при обновлении. А вот с высотой строки дело обстоит иначе. Иногда случается так, что нужно задать строкам сводной высоту больше, чем стандартная. Но при работе со срезами и обновлениями эта высота снова слетает к стандартной.
В некоторых случаях помогает выделение целиком нужных строк и отключении опции "Переносить текст" на Главной. А в некоторых можно использовать другой приём.
Выделяем в любом пустом столбце строки, высоту которых надо фиксировать, и меняем размер шрифта на тот, который даст нужную высоту. При этом можно даже не вводить никакой текст в эти ячейки. Изменение размера шрифта заставит Excel всегда держать высоту строк на определенном уровне (в т.ч. при обновлении сводной и работе со срезами).
#УР2 #Сводные_таблицы
За оформление ячеек по умолчанию в книге Excel отвечает стиль "Обычный". Если вы хотите, например, изменить размер шрифта для всех ячеек в файле (в том числе и для тех листов, которые будут созданы в дальнейшем), то просто изменить настройки формата для этого стиля.
Но нужно помнить о том, что если в ячейках есть текст, к которому было применено форматирование отличное от стиля "Обычный", то он останется без изменений. И все листы, которые будут скопированы из других файлов также сохраняют настройки своих исходных книг.
#УР1 #Оформление_таблиц
Копировать информацию между книгами Excel очень просто. Можно даже целиком перемещать целые листы. Но будьте внимательны при выполнении этой операции. Помните, что вместе с листом "переезжают" не только данные на нем, но много чего еще: именованные диапазоны, числовые форматы, стили ячеек...
Иногда именно это и нужно. А иногда мы просто хотим перенести данные из ячеек в другой файл, без лишнего мусора. В таких случаях лучше всего просто копировать данные и вставлять как значения, а не таскать из файла в файл листы целиком и все сопутствующие довески.
#УР1 #Работа_с_листами_книги
Откройте в себе внимательного аналитика и научитесь превращать обычные таблицы в мощный инструмент для анализа и автоматизации на бесплатном курсе-симуляторе «Excel: простые шаги для оптимизации работы с данными».
Курс подойдёт новичкам и тем, кто уже работает с данными: финансистам, маркетологам и продактам. Вы узнаете, как с помощью формул, сводных таблиц и прочих фишек Excel сделать свою работу проще и эффективнее.
🎁 После бесплатного обучения вы получите в подарок карьерную консультацию и скидку до 50% на курс-профессию "Аналитик данных", чтобы улучшить свои навыки под руководством экспертов.
Присоединяйтесь бесплатно в любое время и убедитесь, что Excel — это не просто таблички.
Реклама. ООО "Нетология". Erid 2VSb5yaFMDz
Когда мы строим сводную, то чаще всего требуется отформатировать числа в области значений, чтобы сделать их более читаемыми (например, добавить разделители разрядов и ограничить число знаков после запятой). В контекстном меню есть две похожие команды: Формат ячеек и Числовой формат. Это не одно и то же.
Первая команда меняет форматирование только выделенных ячеек (или ячейки). А вот вторая - меняет форматы сразу для всех значений выбранного поля. Поэтому лучше использовать именно второй вариант. Тогда при обновлении и расширении сводной новые значения сразу будут отформатированы как нужно.
#УР2 #Сводные_таблицы
Для поиска текста в ячейке используются функции НАЙТИ и ПОИСК. Их минус в том, что если надо будет искать какое-то слово, то будут найдены не только вхождения этого слова как отдельного, но и сложные составные слова (например, молоко найдется в слове молокозавод).
Чтобы этого избежать, можно искать не только само слово, но и пробелы вокруг него. А чтобы исключить ошибку в случае, когда в ячейке с текстом есть только такое слово и больше ничего, можно обернуть пробелами с обеих сторон и эту ячейку тоже.
#УР2 #Примеры_формул
Небольшое дополнение к прошлому уроку. Если вы удалили источник данных сводной, то его можно достаточно быстро восстановить. Нужно построить сводную так, чтобы в ней был отображен общий итог по какому-то полю (без фильтров). Двойной клик по такой ячейке создаст новый лист, на которому будут строки исходных данных, из которых получена ячейка, по которой вы кликнули. А так как это ячейка общего итога, то восстановится исходная таблица данных.
#УР2 #Сводные_таблицы
На макросы в Excel можно назначать горячие клавиши. Это очень удобно и практично. Единственный нюанс - программа различает и раскладки, и регистры. Например, если нам нужно назначить на макрос сочетание CTRL+m, то оно будет работать только при английской раскладке.
Если нужен также запуск и в русской раскладке по тем же клавишам, то можно продублировать макрос и на копию назначить уже сочетание CTRL+ь.
#УР4 #Макросы
Когда мы работаем с большими объемами данных и в одном файле храним и сами данные, и сводные таблицы по ним - такие файлы имеют очень большой размер. Чаще всего можно их немного оптимизировать.
Например, вы можете отключить хранение кэша сводной таблицы вместе с файлом. Это уменьшит размер документа, а единственное неудобство будет такое: при изменении какой-то сводной таблицы ее придется предварительно обновить (о чем Excel честно предупреждает при попытке обновления). Часто это оказывается небольшой ценой за существенное снижение объема файла.
#УР2 #Сводные_таблицы
Выделять диапазоны мышкой удобно, когда они не слишком большого размера. Выделять клавиатурой удобно, когда диапазон уже заполнен. Если же надо выделить большой пустой диапазон какого-то известного адреса, то можно использовать окно "Переход".
Выделяем первую ячейку, жмем F5, вводим адрес второй ячейки и зажав SHIFT кликаем ОК. В итоге быстро и удобно выделяется нужный диапазон.
#Горячие_клавиши
В некоторых задачах появляется необходимость по номеру месяца определить первый рабочий день в этом месяце (разумеется, с учетом выходных и праздников). Решить проблему можно несложной формулой (плюс понадобится отдельный список праздничных и выходных дней).
Идея в том, чтобы определить последний день предыдущего месяца, а затем от этого дня отсчитать дату через один рабочий день. В итоге попадем на первый рабочий в следующем месяце. В основе вычисления - функция РАБДЕНЬ.
#УР2 #Примеры_формул
🤕 Заберите вашу «Аптечку финдира»
Нередко в бизнесе финансовый директор берёт на себя роль врача. Одна из самых частых «болезней», с которой надо разобраться, — нехватка денег. Чтобы их достать, приходится выдумывать сложные схемы. Однако теперь процесс можно упростить..
Воспользуйтесь нашей «Аптечкой финдира» и моментально найдите источники денег в компании. Внутри «аптечки»:
💊 отчёт «ABC-анализ» — покажет самые рентабельные товары и услуги;
💊 модуль «Согласование платежей» — поможет взять под контроль финансовые потоки;
💊 отчёт по дебиторской задолженности — покажет, сколько денег в компании и кто должен вашему бизнесу.
Если сейчас у вас в компании всё хорошо, мы очень рады. Однако на всякий случай лучше всегда иметь под рукой «финансовую аптечку». Чтобы её получить, переходите по ссылке и оставляйте заявку.
ОСТАВИТЬ ЗАЯВКУ
📊 Excel раньше — какие-то таблички для бухгалтеров.
🚀 Excel сейчас — личный аналитик и оптимизатор.
Превращайте скучные цифры в полезные отчёты, умные таблицы и дашборды без всякой магии на курсе «Excel: от основ до анализа данных».
Вас ждут:
- Продвинутые формулы.
- Визуализация данных.
- Power Query и Power Pivot.
Всё это под руководством Павла Козлова, гуру Excel с 15-летним стажем. Он научил более 5000 человек укрощать эту программу. Теперь ваша очередь.
Неважно, новичок вы или опытный пользователь, курс поможет вам стать тем самым, кто делает красоту в Excel. Присоединяйтесь к курсу и открывайте новые возможности для своей карьеры. - https://netolo.gy/dLIs
Реклама. ООО "Нетология". ИНН 7726464125. Erid: 2VSb5wHna7z
При работе со значениями даты и времени часто необходимо извлечь в отдельную ячейку только дату, а в другую - только время. Делается это очень просто, если помнить о том, что дата - это целое число, а время - дробная часть целого числа.
Тогда задача сводится к извлечению целой и дробной части по отдельности. Первую можно достать функцией ЦЕЛОЕ, а вторую можно выразить как остаток от деления числа на единицу (остатком в таком случае будет дробная часть). Формулы очень простые и короткие.
#УР2 #Примеры_формул
Инструмент "Мгновенное заполнение" в Excel есть уже достаточно давно (с 2013 версии). И спектр задач, которые он может помочь решить, достаточно широк. Вот одна из них.
Есть список сотрудников в формате ФИО. Нужно изменить порядок так, чтобы фамилия была в конце. Задача несложная. Можно разбить данные на столбцы с помощью "Текст по столбцам", а потом пересобрать формулой в нужном порядке. Но с мгновенным заполнением это делается ещё быстрее. Вводим пару примеров результата - и Excel угадывает, что мы пытаемся сделать.
#УР1 #Обработка_таблиц
Для получения произведения нескольких ячеек в Excel можно их просто перемножить, а можно использовать функцию ПРОИЗВЕД. Её плюс в том, что она умеет игнорировать пустые ячейки, логические и текстовые значения. А значит, если в какой-то перемножаемой ячейке окажется текст или пустота, Вы не получите ошибку или ноль (в отличие от ручного перемножения).
А если нужно перемножить ячейки без учета нулевых значений (ведь даже один нулевой множитель даст в итоге ноль), то используйте небольшую формулу массива из связки ПРОИЗВЕД + ЕСЛИ. Она простая и вполне решает нашу задачу.
#УР2 #Примеры_формул
Обычно мы нумеруем недели внутри каждого года. Но иногда требуется выполнить возобновляемую нумерацию внутри каждого нового месяца. То есть с 1-ого числа по 7-ое число - первая неделя, с 8-ого по 14-ое - вторая неделя и т.д.
Стандартной функции для такой нумерации нет, но ее легко получить формулами на основе округления или целочисленного деления. Показываем парочку примеров.
#УР2 #Примеры_формул
Знание маленьких хитростей и трюков порой может очень сильно ускорить работу в Excel. Например, чтобы ввести числовое значение в ячейку в текстовом формате, достаточно просто начать ввод с символа апострофа (').
Вводим апостроф, а за ним - нужное число. В итоге Excel воспримет его как текст. Причем апостроф будет виден в строке формул, но не будет виден в ячейке. Разумеется, работает это не только для чисел, но и для значений даты и времени (ведь они, на самом деле, так же являются числами).
#УР1 #Работа_с_листами_книги
У нас тут поинтересовались, как можно вставить формулой текст в нужную позицию в ячейке. Чаще всего задачу можно решить несколькими способами. Например, использовать функции ПОДСТАВИТЬ и ЗАМЕНИТЬ. Первая может заменить нужный символ на любой другой текст, а вторая умеет заменять указанное число символов, начиная с указанной позиции. Ну а есть не указать количество символов для замены, то новый текст будет просто добавлен в нужную позицию.
#УР2 #Примеры_формул
Исходные данные для сводных таблиц не всегда достаточно нормализованы. Одна из частых проблем - числа сохранены как текст. Частный случай этой проблемы - даты, сохраненные как текст. Если построить сводную и поместить такие даты в область строк, то не получится, например, сгруппировать их помесячно.
Исправить проблему легко (мы уже не раз показывали). Тонкий момент заключается в том, что после такого исправления сводную таблицу придется обновить дважды, чтобы даты наконец стали вести себя в ней как даты.
#УР2 #Сводные_таблицы
В сложных и больших файлах часто приходится переключаться на ручной пересчет формул (иначе работа становится крайне неудобной). При этом по умолчанию при сохранении файла пересчет формул все-таки запускается (если что-то изменялось в ячейках, влияющих на формулы). Иногда это может раздражать (при частых сохранениях, например).
Отключить эту опцию можно в параметрах на вкладке "Формулы".
#Справка
Еще один способ сократить размер файла - просто удалить исходные данные, на которых построены сводные таблицы. Сами сводные при этом не пропадут и не сломаются. Их даже можно будет перенастраивать. А вот обновить сводную уже не получится - источник данных будет недоступен. Разумеется, такой способ можно применять лишь тогда, когда вам ТОЧНО не нужны исходные данные и не потребуется обновлять сводные таблицы. Плюс способа - существенное уменьшение размера файла.
#УР2 #Сводные_таблицы
Одна из задач, решаемых в Excel - заполнение каких-то форм на листе различными данными. Если у вас есть такая форма, то наверняка часто приходится очищать поля перед очередным заполнением.
Чтобы сделать такую операцию более удобной, можно выделить все заполняемые ячейки (зажав CTRL), а затем назначить диапазону имя (не забываем нажимать ENTER после ввода в поле Имя). В итоге, можно парой кликов выделить нужный диапазон и нажать DELETE для очистки. А можно и вовсе создать кнопку и привязать на нее коротенький макрос для очистки.
#УР1 #Обработка_таблиц
В Параметрах Excel можно найти много интересных опций и настроек. Одна из них - автоматическая вставка десятичной запятой. Такая настройка полезна, если нужно вручную вводить много данных, содержащих десятичную часть. Ставим галочку и указываем, сколько разрядов нужно отделять запятой. После активации вводим в ячейку числа с десятичными знаками, а Excel сам вставляет запятую. Но будьте аккуратны. Например, чтобы ввести 0,8 при включенной опции вставки запятой для 2 десятичных знаков, надо вводить в ячейку 080 или 80. Если ввести 08, то получим 0,08, а не нужное значение.
Кроме того, важно помнить, что настройка действует на всех листах и во всех файлах. Обычно это не требуется, поэтому после завершения ручного ввода большого количества данных не забывайте деактивировать эту опцию.
#Справка
Когда сводная таблица построена на огромном источнике данных, то перестройка макета может занимать длительное время при каждом мелком изменении (удалить поле, добавить поле и т.д.). Если надо серьезно перестроить сводную, то можно временно включить опцию "Отложить обновление макета". Вы не будете видеть, как меняется сводная, но сможете быстро настроить макет. А после завершения можно уже нажать кнопку "Обновить" или вообще отключить эту опцию.
#УР2 #Сводные_таблицы
Для математического округления чисел в Excel есть множество встроенных формул (ОКРУГЛ, ОКРВНИЗ, ОКРВВЕРХ и т.д.). Но бывают задачи, где требуется нестандартное округление. Разберем одну из таких.
Есть числа с десятичными знаками. Нужно округлить их по следующему правилу:
- если десятичная часть больше 0,75, то округляем вверх (до следующего целого)
- если меньше 0,75, то округляем вниз (до предыдущего целого).
Решается простой функцией ЕСЛИ и функциями для извлечения целой части числа (ЦЕЛОЕ) и его дробной части (ОСТАТ). А те, кто любит более изящные формулы, могут использовать вариант без функции ЕСЛИ.
#УР2 #Примеры_формул