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

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

На клавиатуре есть такая не очень популярная клавиша как Scroll Lock. Многие пользователи вообще никогда её не нажимают. Но с этой клавишей связана одна интересная особенность работы Excel.

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

#Справка

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

Порядок действий тот же. Создаем динамический диапазон с помощью формулы (СМЕЩ или ИНДЕКС), а затем указываем имя этого диапазона в источнике данных для Спарклайна. Но есть и ограничение - данный метод не работает для двумерных диапазонов, которые указываются источником для группы спарклайнов. Возможно использовать только с одиночными графиками.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Достаточно использовать в формуле функцию ЧИСТРАБДНИ для подсчета количества рабочих дней. Если у вас есть список праздничных дат, то можете разместить их в диапазон ячеек и указать третьим аргументом функции. Тогда эти дни не будут считаться рабочими при расчете.

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

Наверняка многие из вас работали с зависимыми выпадающими списками. Когда набор значений во втором списке зависит от того, какой элемент выбран в первом. Проблема таких списков в том, что зависимость существует только в одну сторону: от родительского списка к дочернему. Выбрав что-то из дочернего списка мы можем вернуться в родительский и поменять значение в нем. При этом во второй ячейке останется выбранным элемент из другого списка. Появится несогласованность данных.

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

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

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

Вы маркетолог, аналитик, bi дизайнер, стратег, продакт, mlщик и просто отличный человек интересующийся данными?
Тогда нужно собирать чемоданы, покупать билеты и мчать на лучшую конфу 2019 года в области аналитики:

Матемаркетинг 2019

- Спикеры со всего мира;
- Современные кейсы от Avito, Яндекс, Skyeng, Amplitude, Flo, Bookmate, MIRO и других;
- Круглые столы;
- Практические воркшопы;
- Море позитива и хорошего настроения.

Детальная программа ЗДЕСЬ

Кстати, мы там тоже выступаем, так что увидимся и познакомимся👍

И главное ТОЛЬКО СЕГОДНЯ - 25.10.2019.
В день Маркетолога - СКИДКА 25%
Промокод на сайте конференции
Не упустите свой шанс в развитии😉

Расчет нарастающего (накопительного) итога нужен довольно часто. В Excel его можно сделать с помощью одной лишь функции СУММ. Главная фишка - в правильном задании ссылки на диапазон.

Закрепляем первую часть ссылки (первую ячейку суммируемого столбца), а вторую - оставляем без закрепления. Копируем вниз - и готово.

Кстати, можно вставить эту же формулу с помощью Быстрого анализа (Excel 2013 и новее)
#УР2 #Примеры_формул

В Excel нет специальной функции для вычисления номера квартала, к которому принадлежит та или иная дата. Но есть функция МЕСЯЦ(), которая позволяет извлекать номер месяца.

А уж зная номер месяца, можно легко вычислить и номер квартала. В виде арабского или римского числа, если нужно (помните, что в случае с Римским числом, на выходе будет не числовой тип, а текстовый).
#УР2 #Примеры_формул

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

Опция находится в Параметрах на вкладке Формулы
#Справка

Когда Вы строите диаграмму позиций в рейтинге - первая позиция должна быть самой верхней. Чтобы отобразить график именно так, включите настройку "Обратный порядок по оси" для оси Y
#УР3 #Диаграммы

Сводную можно построить так, что в строках/столбцах будет > 1 поля. Тогда может потребоваться объединение ячеек. Простая команда не сработает, но аналог есть в Параметрах сводной
#УР2 #Сводные_таблицы

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

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

Она пригодится, когда сводная фильтруется по принципу: "Показать все значения поля, кроме тех, которые мы исключаем". Дело в том, что если в исходные данные добавятся новые значения, то они по умолчанию не отображаются фильтром.

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

Матемаркетинг 2019.
Вся команда здесь.

Открыты для очного общения 😉

Зал Архимед 1.
Мастер класс.
Excel. Каждой задаче свой инструмент.

Файлы с которыми работаем прямо сейчас, архив.
http://bit.ly/2CIQ07W

Для поиска слова в каком-то тексте используются ПОИСК() и НАЙТИ(). Но они отличаются. ПОИСК() не чувствителен к регистру и позволяет использовать подстановочные знаки
#УР2 #Применение_встроенных_функций