Хотите, чтобы в фигуре отображался какой-нибудь текст, сформированный формулой?

Например, текущее время с какой-нибудь надписью (Текущее время: 12:00") или что-то другое ("Выручка на дату 01.06: 1,2 млн")?

Для этого формируем в ячейке текст формулой, а потом ссылаемся на ячейку с формулой из фигуры (выделяйте фигуру, вводите знак "равно" и кликайте по ячейке, как в обычной формуле).

В примере используется функция ТДАТА / NOW — это текущие дата и время. И функция ТЕКСТ / TEXT — напоминаем, при объединении в текст числовых (а дата и время = число) значений они теряют форматирование. Если вам нужно время в заданном формате, например, ЧЧ:ММ, используйте функцию ТЕКСТ, которая превращает число в текст, но в нужном формате.

Читать полностью…

Дано: есть данные за несколько лет с выручкой (или чем-то еще) по дням.

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

Для этой задачи извлечем из чемоданчика всемогущий мультитул — сводную таблицу. По умолчанию в сводной даты группируются по годам-кварталам-месяцам, то есть мы смотрим на данные в рамках каждого года. А нам нужно убрать этот верхний уровень, смотреть только на уровень месяцев (или кварталов, если вам нужно сезонность на этом уровне). Для этого группируем данные сами — только по месяцам.
Это можно сделать на ленте: Анализ сводной таблицы — Группировка по полю
или в контекстном меню — щелкаем по полю с датами в сводной правой кнопкой мыши и нажимаем "Группировать" (или нажимаем Г на клавиатуре)

После группировки можно посмотреть на сумму показателя по месяцам, а можно на среднее значение. Еще раз уточняем: теперь это данные за все январи в периоде , то есть мы не смотрим на динамику во времени, а смотрим на сезонность! Если нам нужна динамика от месяца к месяцу, то нужна группировка и по годам, и по месяцам, как было изначально при построении сводной (в большинстве версий Excel поле с датами само группируется в таком формате при его переносе в область строк)

Смотрим на видео!

Читать полностью…

Как разобраться в вышмате за 1 вечер? 😨

Да никак. Но можно научиться понимать математику. И сэкономить месяцы жизни, килограмм нервов и сил.

И поможет вам в этом единственный канал в телеграме о высшей математике. Его автор - выпускник СПБГУ, а ныне — преподаватель предмета.

На простом языке разбирает сложные вещи, даёт шпаргалки и проводит эфиры с решением задач 📈

Находка для студентов и тех, кому нужен вышмат по работе. От полезных материалов и книг до ответов на вопросы и уроков.
Посмотрите сами 👉 @lav_math

Реклама. Люлинцев Андрей Валерьевич. Erid: 2VtzqxFDhbv

Читать полностью…

Режим перехода в конец (End mode)

Нажмите клавишу End — и в строке состояния появится надпись "Режим перехода в конец" (End mode)

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

Читать полностью…

Повторное применение фильтра

Вы поставили фильтр (Ctrl + Shift + L, кстати). Поменяли что-то в данных. И вот некоторые строки, в которых вы вносили изменения, уже фильтрации не соответствуют. Если добавились новые данные в конце таблицы — они тоже не отфильтруются автоматически. Как отобразить актуальные данные?
Не нужно отключать фильтр и настраивать снова.

Просто нажимайте Ctrl + Alt + L / . Или кнопку "Повторить" (Reapply) на ленте инструментов рядом с кнопкой фильтра (на вкладке "Данные" / Data).

Читать полностью…

Возможно, что в совершенстве владея инструментами Google, вы не замените тех, кто в них не разбирается. Но это и не наша цель, главное — вы сможете упростить себе работу (да и жизнь в целом), а еще дополнить свое резюме 😎

Приглашаем на практикум «Google Таблицы: магия формул» ✨ Всего за 4 занятия вы научитесь пользоваться бесплатным и полезным инструментом и придадите буст х2, х3, а может, и все х10 своим процессам 💫

Мы изучим формулы в Google Таблицах с нуля. Эти знания пригодятся и для Excel, и для наших офисных пакетов, например, Р7-Офис. Научимся решать нестандартные задачи, опираясь на общие правила работы с формулами в Excel и Google Таблицах. А также попробуем создавать свои формулы.

Практикум ведет Ренат Шагабутдинов — преподаватель и консультант по Google Таблицам и Excel с опытом 10+ лет. Имеет сертификацию MOS Excel Expert. Настраивал отчетность и автоматизацию в МИФе, МТС и «Автомире». Автор курсов и книг по Google Таблицам. Основатель телеграм-каналов «Google Таблицы» и «Магия Excel».

Переходите по ссылке, чтобы посмотреть подробную программу практикума и записаться 🪄 mif.to/zTZh3

Читать полностью…

И еще один совет по работе с объектами, которые находятся "над ячейками" (на графическом слое).

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

Читать полностью…

Хотите скрыть все объекты на графическом слое Excel? Нажмите Ctrl + 6.

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

Чтобы все это дело временно убрать, нажмите Ctrl + 6. А еще одно нажатие этого сочетания вернет все на место.

Спасибо каналу "Финансовый Гиппогриф" за этот лайфхак 😺

Читать полностью…

Добавляем гистограммы в сводной таблице отдельным столбцом

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

В двух словах: мы добавляем еще один столбец с теми же суммами, применяем к нему условное форматирование (это могут быть не только гистограммы, но и значки / цветовая шкала) и потом в настройках правила условного форматирования включаем опцию "Показывать только столбец" (Show Bar Only).

Читать полностью…

Учите Python, SQL и Power BI. Становитесь аналитиком данных с зарплатой 130 000 рублей

Даже переезжать никуда не придётся: аналитиков данных ищут «Сбер», «Леруа Мерлен», «Альфа-Банк», «Ростелеком», «Литрес», «Магнит», СДЭК — по статистике, 45% компаний на российском рынке.

Чтобы покрыть дефицит кадров, Skillbox даже открыл бесплатный доступ к курсу «Аналитик данных с нуля».

✅ Авторы курса — аналитики из «Иви», банка «Открытие» и Badoo.
✅ Первый доход уже во время обучения.
✅ Поможем найти работу через 6 месяцев.
✅ Обучение на практике: вы выполните 35 заданий и будете готовы к любым задачам.

93% выпускников Skillbox говорят, что они достигли поставленной цели. Убедитесь сами: https://epic.st/h-3okq?erid=2VtzqvL7Xmx

Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880

Читать полностью…

Отличия по строкам
Вы хотите быстро выделить цветом ячейки, в которых план отличается от факта (один столбец от другого — в общем случае)?

1 Выделяем столбцы (можно быстро быстро выделить их сочетанием Ctrl + Shift + стрелка вниз)
2 Ctrl + G —> Выделить (Special)
3 Отличия по строкам (Row differences)
4 Красим выделенные ячейки нужным цветом. Готово!

Смотрим на GIF (без звука)

Читать полностью…

Услышал звон – узнай, откуда он

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

Какие возможности даёт сервис:
- Запись разговоров с клиентами для обновления скриптов
- Анализ рекламных каналов: от баннеров и роликов до наружной рекламы
- Легкая интеграция с CRM-системами: 1С. Битрикс24 и др.
- Широкий выбор нумерации: 8-804/800, городские и мобильные номера
- Интуитивно понятный личный кабинет

*Обработка аудиозаписей осуществляется программным обеспечением билайн (18+) при наличии у клиента правовых оснований на соответствующую обработку. Функционал услуги доступен при получении согласия пользователя на запись разговора.

Перейти на сайт

#реклама
beeline.ru
О рекламодателе

Читать полностью…

Анпивот (Unpivot) — отмена свертывания, преобразование двумерной таблицы в плоскую, пригодную для построения сводной таблицы.

Эту операцию можно проделать в Power Query:
— Данные — "Из таблицы/диапазона" (выделяем предварительно исходную двумерную таблицу)
— Выделяем в редакторе Power Query те столбцы, которые должны остаться в новой таблице — у нас это "Компания".
— Преобразование — Отменить свертывание других столбцов (Transform — Unpivot Other Columns)

Альтернатива — формулой, если у вас Microsoft 365 с волшебными новыми функциями. Про формулу напишем отдельно!

Читать полностью…

Автозамена (AutoCorrect). Если вам часто нужно вводить какое-то словосочетание, адрес, имейл и так далее — придумайте для него короткое обозначение и добавьте в список автозамены в Параметрах.
Параметры → Правописание → Параметры автозамены
Options → Proofing → AutoCorrect Options

Обратите внимание, что список автозамены один на все приложения Microsoft Office! Так что введенные вами варианты будут заменяться и в Outlook, Word и т.д.

Читать полностью…

Контрольное значение: отслеживаем значения ячеек в любом месте

Если вам нужно всегда видеть, чему равно значение в какой-нибудь ячейке, даже если вы работаете на другом листе — добавьте эту ячейку в окно контрольного значения.

Лента инструментов:
Формулы — Окно контрольного значения
Formulas — Watch Window

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

Читать полностью…

Нужно выделить все формулы?

Нажимаем Ctrl+G

В открывшемся окне "Переход" нажимаем "Выделить" (Special)

Далее — "Формулы" (Formulas)

Готово. Можно теперь покрасить ячейки с формулами, если хочется.

Читать полностью…

Средневзвешенная цена

Если у нас есть данные по продажам товаров с разными ценами, мы не можем считать среднюю цену просто функцией СРЗНАЧ / AVERAGE. Ведь мы не будем учитывать число проданных товаров, и, как в примере, средняя цена будет некорректная.

= (650 + 1400 + 2200) / 3 = 1417.

Но мы продали совсем мало товаров за 2200 и за 1400! Фактическая средняя цена должна быть ближе к 650.

Поэтому правильно вычислить сумму продаж в деньгах и потом поделить на все штуки.

Сумму продаж в деньгах можно вычислить одной функций СУММПРОИЗВ / SUMPRODUCT — она перемножает числа из нескольких диапазонов и потом суммирует результаты. Останется только разделить результат на сумму проданных штук. И получится 752 — это уже похоже на правду!

Читать полностью…

Заполняем промежуточные шаги с помощью прогрессии

Допустим, вы знаете первое значение и то, к которому нужно прийти. Выделите весь диапазон от первого значения до последнего и вызовите инструмент "Прогрессия" (Series):
Главная — Заполнить (кнопка со стрелкой вниз в правой части ленты) — Прогрессия

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

Читать полностью…

Добавляем к дате день недели и выделяем выходные

Допустим, мы с вами хотим видеть в каждой дате день недели — не "01.01.2024", как по умолчанию, а "01.01.2024 Пн".

Для этого заходим в формат ячеек (Ctrl + 1) и добавляем к формату "ДДД" (DDD). Это краткое обозначение дня недели ("Пн"). Для полного ("Понедельник") понадобится код "ДДДД" (DDDD).

Ну а чтобы выделить цветом выходные (или другие дни) — воспользуемся условным форматированием (Conditional Formatting).
Зададим правило с формулой, а в ней будем использовать функцию ДЕНЬНЕД / WEEKDAY.
Она возвращает порядковый номер дня недели. Чтобы нумерация была привычной для нас с вами, добавьте второй аргумент, равный двойке:

=ДЕНЬНЕД (ячейка с первой датой в диапазоне; 2)

Тогда понедельнику будет соответствовать единица (иначе неделя будет начинаться с воскресенья, если пропустить второй аргумент функции), вторнику — двойка и так далее.

И остается добавить условие — день недели у нас должен быть больше 5 (то есть 6 или 7, суббота или воскресенье), чтобы ячейка заливалась цветом.

Читать полностью…

Нужно напечатать много строк, которые точно не влезут на одну страницу? Закрепляем строки, чтобы заголовки были на каждой странице при печати!

Разметка страницы — Печатать заголовки — Сквозные строки
Page Layout — Print Titles — Rows to repeat at top

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

Читать полностью…

Автосумма: одним движением суммы по всем столбцам/месяцам.

Сочетание клавиш Alt + = позволяет получить сумму быстро, не вводя руками функцию СУММ / SUM.
Если выделить ячейку под столбцом с числами и нажать Alt + =, то получим сумму по этому столбцу (одну функцию СУММ).
Уточняем: речь про "просто Alt", то есть левый Alt. Правый Alt заменяет сочетание Ctrl+Alt и в сочетании с плюсом-минусом будет менять масштаб листа.

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

Читать полностью…

🤯 Каждый день рутина — офис, таблицы, отчёты. Ещё и платят мало. В то же время 1С-разработчики работают на удалёнке и получают до 300 000 р.

Самое время стать одним из них!

Чтобы освоить 1С, не требуется опыт в IT и знания английского! На онлайн-курсе «Профессия 1С-разработчик» от Skillbox вы всего за 6 месяцев научитесь:

автоматизировать бизнес-процессы;
проектировать архитектуру баз данных;
разрабатывать конфигурации и системы учёта;
интегрировать «1С» с другими системами.

В конце курса вы получите свидетельство 1C и помощь в трудоустройстве.

Где вы сможете работать:

💰 1С используют более 12 000 компаний, среди которых Лукойл, Сибур и Газпром.
🌴 Есть удалёнка и фриланс.

Открываем бесплатный доступ к первым 5 модулям. Вы учитесь совершенно бесплатно и решаете, стоит ли продолжать.
Узнать подробнее о курсе и получить скидку можно здесь: https://epic.st/CKANs?erid=2VtzqwmaWDs

Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880

Читать полностью…

Сегодня всем подписчикам канала мы открыли бесплатный доступ к курсу "Инструменты Excel для финансиста" в Высшей школе финансового директора.


Изучив курс, вы сможете:

🔷Создавать в Excel динамические графики и гистограммы

🔷Настроить дашборд с метриками под нужды вашей компании

🔷Создать панель оперативного мониторинга и отслеживать показатели

🔷Построить интерактивный график для анализа затрат в Excel


Скачивайте дашборды, инструкции, матрицы ключевых метрик, шаблоны с диаграммами и применяйте их в своей работе!


👉Заказать бесплатный доступ к курсу на 3 дня: https://clck.ru/39xAYd

Читать полностью…

Объединяем умные таблицы в одну: формулы и Power Query

В видео разбираем такую задачу: собрать данные из нескольких умных таблиц.

Если у вас Microsoft 365, то можно наслаждаться новыми формулами и использовать функцию ВСТОЛБИК / VSTACK. Так же в видео разбираем, как с ее помощью в сочетании с функцией ФИЛЬТР / FILTER фильтровать данные "в режиме реального времени" и добавить к результату фильтрации заголовки.

Если версии 2010 и новее, то можно с помощью Power Query объединить таблицы в один запрос и далее анализировать данные вместе с помощью сводной таблицы или просто выгрузить на лист как одну таблицу.

Читать полностью…

Видео для новичков: абсолютные и относительные ссылки в Excel

Друзья, вашему вниманию видео по фундаментальной для понимания табличных формул (ссылки работают +- одинаково и в Excel, и в Google Таблицах, и в других офисных пакетах) теме:
— Абсолютные и относительные ссылки
— Смешанные ссылки
— Стиль ссылок R1C1
— Режим "Показать формулы"
— Бонус: почему объединенные ячейки могут портить жизнь при работе с формулами

8 минут со звуком:
https://youtu.be/jCgDpHkmE0s

Читать полностью…

Самый компактный вариант для визуализации прогресса в Excel — гистограмма (условное форматирование)

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

Сделать такой простенький прогресс-бар можно так:
— Сослаться на фактическое значение в отдельной ячейке, куда мы вставим гистограмму;
— Вставить гистограмму в эту ячейку по вкусу;
— Задать в настройках правила условного форматирования максимальное значение как план/цель — тогда гистограмма будет шириной во всю ячейку, когда факт будет равняться плану. Иначе — будет показываться % выполнения;
— Можно включить опцию "Показывать только столбец", фактическое значение нам здесь не нужно — оно и так есть в исходной ячейке. А тут пусть будет только наш прогресс-бар.

Читать полностью…

А вот формула-монстр для отмены свертывания (анпивот) "в режиме реального времени". Но только с новыми функциями 365.

В общем виде формула такая:

=ГСТОЛБИК(ПОСТОЛБЦ(ЕСНД(РАЗВЕРНУТЬ(первый столбец;;СЧЁТЗ(заголовки столбцов, для которых отменяется свертывание));первый столбец));
ПОСТОЛБЦ(ЕСНД(РАЗВЕРНУТЬ(заголовки столбцов;СЧЁТЗ(первый столбец - для подсчета числа значений в нем));заголовки столбцов));
ПОСТОЛБЦ(данные столбцов))

Тут на верхнем уровне три вертикальных массива, которые склеиваются в одну таблицу функцией ГСТОЛБИК / HSTACK.

Последний аргумент с функцией ПОСТОЛБЦ / TOCOL — это просто данные (суммы в нашем случае), которые превращаются из двумерного массива в один столбик за счет этой самой TOCOL.

Первые два аргумента — это столбцы с компаниями и с заголовками. Здесь работает функция EXPAND / РАЗВЕРНУТЬ: она добавляет к массиву с названиями компаний два лишних пустых столбца, заполненных ошибкой Н/Д. Которую мы заменяем на исходные данные с помощью ЕСНД / IFNA. Иначе говоря, мы клонируем каждое название компании три раза (по числу столбцов с данными), потом превращаем в одномерный массив с помощью ПОСТОЛБЦ / TOCOL.

В отдельном сообщении выше файл с формулой с пошаговым разбором отмены свертывания — забирайте, изучайте и пользуйтесь!

Читать полностью…

Работа с отчётами, таблицами и графиками отнимает кучу времени? Нужно отчитаться за работу перед начальством в виде ПРЕЗЕНТАЦИИ, а выделить самое важное и наглядно показать результат не получается?

А что, если от этого будет зависеть твоя карьера?

Записывайся на мастер-класс по отчётам и таблицам в PowerPoint или Keynote от академии презентаций Bonnie&Slide и научись создавать понятные и эффективные отчёты за счет правильной компоновки данных и шаблонов, чтобы получить повышение на работе:

- Без дизайнеров

- По брендбуку или без

- Не тратя более 10 минут один слайд

Регистрация тут - ссылка

🎁Все, кто зарегистрируется, получат гайд "10 способов как сделать убойную понятную презентацию, когда много данных" в подарок!

Реклама. ООО «БОННИ И СЛАЙД». ИНН 9701259086.

Читать полностью…

💼 Освойте профессию с нуля и найдите работу через 6 месяцев!
Если думаете сменить специальность или начать карьеру с нуля, ищите возможность быстро получить нужные навыки. Сейчас важно как можно скорее выходить на первый заработок.

Например, на курсе Skillbox вы получите востребованную профессию BI-аналитика.
Это практический курс, над которым работали аналитики банка «Открытие». На нём вы научитесь:
💪 настраивать систему аналитики;
💪 создавать прикладные дашборды в Power BI;
💪 анализировать поведение пользователей с помощью API и Google Analytics;
💪 работать с данными в SQL и Python.

В конце курса у вас будет 3 проекта в портфолио.

Мы гарантируем ваше трудоустройство — или вернём деньги.
Узнать подробнее о курсе и получить скидку можно здесь: https://epic.st/aEC3rZ

Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880

Читать полностью…

Всё ещё собираете и обрабатываете данные вручную? Попробуйте SQL! Освойте и полюбите язык, который пригодится программисту, продакт-менеджеру, BI-аналитику, data-инженеру или специалисту по Data Science.

Зарегистрируйтесь на мини-курс и получите 5 полезных статей по SQL и Excel: https://epic.st/zEM8pW

Вас ждут:
— Море полезной теории
— Практические работы для закрепления навыков
— Бонусы: скидка 10 000 рублей на любой курс Skillbox, бессрочный доступ к материалам, полезные чек-листы и год бесплатного изучения английского языка
— Персональная карьерная консультация

Что будем делать:
— Писать запросы на языке SQL
— Проводить аналитику для бизнеса
— Разрабатывать автоматизированную отчётность в Excel
— Обрабатывать данные в Power Query
— Визуализировать показатели в Excel: будете создавать красивые графики, диаграммы и отчёты
— Применять инструменты Excel для анализа данных

Спикер — Мкртич Пудеян, специалист по анализу данных в «Газпромбанке». Сертифицированный SQL-разработчик от Microsoft, 8 лет работал специалистом по хранилищам данных в Tele2.

Оставьте заявку и получите доступ к мини-курсу прямо сейчас.

🌟Откройте новые возможности для карьеры!

Реклама. ЧОУ ДПО «Образовательные технологии «Скилбокс (Коробка навыков)», ИНН: 9704088880

Читать полностью…