Магия Excel

Привет, друзья! Записал курс, над которым трудился несколько месяцев — для учебного центра МГУТУ (Московский гуманитарный университет технологий и управления) — Более 60 уроков и 18 часов видео — Разумеется, видео с хорошим качеством, чистым звуком, масштабированием — Все исходные и готовые файлы к каждому уроку — Тесты после каждого модуля — Домашки с самопроверкой и примерами решений — Все темы от основ и формул до самых новых функций и LAMBDA, Power Query (и немного Power Pivot), Google Таблиц и макросов — Новые примеры: кофейня "Бодрый барсук", автосервис "Колесо Фортуны" и зоотовары "Лапки и хвостики". И их неидеальные данные, конечно Детали тут — если искали подробный и максимально свежий курс по Excel для себя или сотрудников — обратите внимание: https://mgutu.ru/courses/computers/basicexcel/

Вводим формулу с новыми функциями по старому (Ctrl + Shift + Enter) Да, если у вас есть функция из новых, возвращающая динамический массив (например, СОРТ / SORT), а вы хотите заранее ограничить размеры результата, можно по старинке выделить диапазон нужного размера и нажать Ctrl + Shift + Enter, то есть ввести формулу как старую формулу массива. Например, вы хотите отсортировать данные по сумме и взять ТОП-3 сделок: выделяете диапазон в три строки с нужным числом столбцов, вводите функцию СОРТ и далее заклинание CSE. Конечно, в конкретном примере можно использовать и функцию ВЗЯТЬ / TAKE, если она есть в вашем Excel — она вернет только заданное число строк / столбцов с начала / конца массива. Второй вариант формулы на скриншоте.

Горячие клавиши по понедельникам🔥 Сегодня немного экзотики для отъявленных любителей клавиатуры! Если вы просто нажимаете Alt (или F10 или /), вы получаете возможность перемещаться по ленте инструментов с помощью клавиш. Это мы уже неоднократно обсуждали. А если нажимать Shift + F6, то будут активироваться разные области - не только лента, но и поле "Имя", строка состояния, ярлыки листов, сам рабочий лист. Таким образом, вы сможете стрелками выбирать запись макросов, масштаб и разные режимы отображения листа (когда у вас будет активна строка состояния). Или добавить новый лист с клавиатуры, когда активируете ярлыки листов. * Ctrl + Tab - перемещение между вкладками диалоговых окон (например, между "Найти" и "Заменить" в окне... "Найти и заменить". С Shift'ом в обратную сторону. * А еще без мышки можно отобразить сообщение об ошибке, если у вас активна ячейка с ней! Это Alt + Shift + F10

Вставка текущих даты и времени в ячейки Если нужностатично (вставили и значение осталось неизменным): Если при открытии Excel у вас была русская раскладка: Ctrl + Shift + 4 для даты Ctrl + Shift + 6 для времени Ctrl + Shift + 4 + пробел + Ctrl + Shift + 6 для даты и времени в одной ячейке Если была английская: Ctrl + ; для даты Ctrl + Shift + ; для времени Ctrl + ;+ пробел + Ctrl + Shift + ; для даты и времени в одной ячейке _ _ _ Если нужно с обновлением: Функция СЕГОДНЯ() / TODAY() для даты. Функция ТДАТА() / NOW() для даты и времени. У обеих функций нет аргументов. Скобки нужны, чтобы Excel понял, что это функция, а не имя, и не возвращал ошибку. Но аргументов не будет. Не можем мы с вами повлиять на ход времени и изменить в этом деле какие-то параметры :)

Вытаскиваем из даты всякое разное: подборка функций и формул Нужно получить номер квартала или посчитать число пятниц в периоде? Получить начало и конец месяца для заданной даты? Ловите пачку полезных формул для работы с датами в Excel! Конец месяца: =КОНМЕСЯЦА(дата;0) Начало месяца: =КОНМЕСЯЦА(дата;-1)+1 Месяц: =МЕСЯЦ(дата) День: =ДЕНЬ(дата) Год: =ГОД(дата) День недели цифрой: =ДЕНЬНЕД(дата;2) День недели текстом: =ТЕКСТ(дата;"ДДДД") 10 рабочих дней от даты: =РАБДЕНЬ(дата;10) Рабочих дней в месяце: =ЧИСТРАБДНИ(КОНМЕСЯЦА(дата;-1)+1;КОНМЕСЯЦА(дата;0)) Кол-во вторников в месяце: =ЧИСТРАБДНИ.МЕЖД(КОНМЕСЯЦА(дата;-1)+1;КОНМЕСЯЦА(дата;0);"1011111") Квартал - вариант 1: =ЦЕЛОЕ((МЕСЯЦ(дата)+2)/3) Квартал - вариант 2: =ВЫБОР(МЕСЯЦ(дата);1;1;1;2;2;2;3;3;3;4;4;4) Номер недели (ГОСТ): =НОМНЕДЕЛИ.ISO(дата)

Горячие клавиши по понедельникам🔥 Сегодня прячем и возвращаем на место всякое: Ctrl + F1 — сворачиваем ленту (напоминание: для этого подойдет и двойной клик по заголовку вкладки ленты) Ctrl + 8 — убираем и возвращаем кнопки для группировки. Сама группировка, скрытые строки — не будут затронуты. Ctrl + 6 — скрываем все объекты на графическом слое (диаграммы, фигуры, флажки — которые не в ячейках, а старые, и другие элементы управления, срезы и временные шкалы).

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

Функция ЗНАЧЕН / VALUE делает текст числом. Но она не сработает (желтые ячейки), если это числа из какой-то чужой локали (с другими региональными настройками, то есть с другими разделителями групп разрядов и/или десятичным разделителем) А вот ЧЗНАЧ / NUMBERVALUE имеет аргументы для этого - можно задать определенные разделители. Но надо быть осторожными — если попадутся числа из российской локали (и в виде чисел, и в виде текста), то запятая (в России — десятичный разделитель) будет считаться разделителем разрядов, то есть мы увеличим числа на несколько порядков (красные ячейки).

Горячие клавиши по понедельникам🔥 Группировка. Alt + Shift + стрелки вправо или влево (группировать или разгруппировать) Что будет происходить — будет зависеть от того, что выделено у вас в Excel: 1 Ячейки. Тогда Excel поинтересуется, строки или столбцы вы хотите группировать / разгруппировать 2 Строки или столбцы. Тогда уже не поинтересуется. 3 Значения в области строк или столбцов сводной таблицы. Тогда Все будет зависеть от того, какие там данные: дата / время или числа — если выделено одно значение, появится диалоговое окно для группировки. Если несколько — они будут выделены в отдельную группу текстовые значения — будут сгруппированы те значения, что были выделены.

Мгновенное заполнение (Flash Fill) — когда вы нажимаете Ctrl+E (или включаете мгновенное заполнение с ленты инструментов, Главная — Заполнить), Excel анализирует всю строку, а не один столбец. Так что можно собирать данные из нескольких столбцов — Можно исправлять регистр текста — Можно добавлять символы, например, точки Все это — в примере с ФИО, где мы добавляем точки, делая инициалы вместо полных ИО, собираем данные из трех столбцов, исправляем регистр. Все это без формул и быстро :) Но только с версии Excel 2013 включительно.

Чем занимается аналитик данных — открытый урок по Python и SQL 🎓 Приглашаем вас на открытый онлайн-урок Нового технологического университета, где вы увидите, как аналитики работают с данными в реальных задачах. На занятии вы: ➡️ поймете, кто такой аналитик данных и чем он занимается ➡️ выполните две практические задачи на Python и SQL, даже если ни разу этого не делали ➡️ разберетесь, стоит ли идти в профессию сейчас, и что будет с рынком IT через 1-3-5 лет ➡️ поймете, как стать аналитиком данных в 2026, даже если вы еще учитесь в ВУЗе Урок подойдет, даже если у вас нет опыта в программировании или аналитике. Спикер — Ева Панкратова, руководитель продуктовой аналитики в М2, ex-Райффайзенбанк. Занятие пройдет онлайн, участие бесплатное. Сразу после регистрации вы получите бонусы: сборник идей для портфолио и памятку по основам SQL и Python 😊 Регистрируйтесь→ссылка

Двойной щелчок в Excel Где он может пригодиться: — Двойной щелчок по названию вкладки ленты инструментов скрывает и раскрывает ленту — По кисточке "Формата по образцу". Если на нее щелкать один раз — то вы сможете применить формат выделенной ячейки один раз к другой ячейке / диапазону. А если дважды — то будете в режиме форматирования по образцу, пока не нажмете Esc. То есть сможете форматировать много отдельных ячеек и диапазонов, выделяя их. — По названию (ярлыку) листа — можно его переименовать. По тексту в фигуре — отредактировать текст. — По границе выделенной ячейки — перемещение в конец диапазона (например, если щелкнуть дважды на нижний край ячейки , то это будет аналог Ctrl + ↓, перемещение в конец диапазона вниз — до последней заполненной ячейки) — В сводной таблице можно "провалиться" до исходных данных. Двойной клик по ячейке = создание отдельного листа с данными, которые сформировали то значение в сводной, по которому вы кликнули дважды. — Конечно же, по правому краю заголовка столбца, чтобы изменить его ширину (или ширину всех выделенных столбцов) автоматически ровно так, чтобы все данные отображались полностью. — Наконец, двойной клик по самому-самому левому верхнему углу окна Excel = закрытие книги.