Магия Excel

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

Делим текст на отдельные символы формулой. Варианты для разных версий Excel Excel 365 =РЕГИЗВЛЕЧЬ(ячейка;".";1) Извлекаем с помощью РЕГИЗВЛЕЧЬ / REGEXEXTRACT любой символ (точка в регулярках = любой символ). Чтобы получить не только первое совпадение, а все символы, задаем третий аргумент, равный единице. Иначе извлекается только первый символ. Excel 2021 =ПСТР(ячейка;ПОСЛЕД(;ДЛСТР(ячейка));1) Формируем последовательность чисел функцией ПОСЛЕД / SEQUENCE — от единицы до числа символов в тексте (ДЛСТР / LEN выдаст число знаков). И извлекаем функцией ПСТР / MID символы. Эта функция возвращает один или несколько (у нас один — это задано в третьем аргументе) символов с заданной (во втором аргументе) позиции. А у нас позиций будет много: сформированная последовательность чисел от 1 до числа знаков. То есть мы вытащим первый, второй и так далее до последнего символа из ячейки. Любая версия =ПСТР($A$1;СТОЛБЕЦ()-1;1) Здесь нужно будет формулу протянуть, это не формула массива, а отдельная для каждого символа. Функция СТОЛБЕЦ / COLUMN возвращает номер столбца. Так как у меня символы в примере извлекаются в столбце B и далее, я вычитаю единицу из номера столбца. Чтобы начать с первого символа.

Вы хотите изучить конкретную тему в рамках Excel. Вот по одной книге на каждую. Excel в целом Microsoft Excel Inside Out (Office 2021 and Microsoft 365) На русском: Excel 2019. Библия пользователя — Куслейка, Александер Макросы Microsoft Excel VBA and Macros — Bill Jelen На русском: Excel 2016. Профессиональное программирование на VBA — Александер, Куслейка (не пугайтесь версии 2016 — макросы не меняются десятилетиями) Сводные таблицы Сводные таблицы в Microsoft Excel 2021 и Microsoft 365 — Джелен Power Query Скульптор данных в Excel с Power Query — Николай Павлов или / и Приручи данные с помощью Power Query в Excel и Power Bi — Пульс, Эскобар Язык M The Definitive Guide to Power Query (M): Mastering complex data transformation with Power Query (книга для глубокого погружения именно в язык M, то есть ее лучше читать с опытом работы в интерфейсе Power Query и при желании решать там нестандартные задачи и писать код самостоятельно) Power Pivot и язык формул DAX (который используется и в Power BI / других решениях Microsoft) Анализ данных при помощи Microsoft Power BI и Power Pivot для Excel — Руссо, Феррари Очень глубоко и основательно про DAX: Подробное руководство по DAX: бизнес-аналитика с Microsoft Power BI, SQL Server Analysis Services и Excel — Руссо, Феррари Для первого ознакомления с Power Pivot можно начать с глав в книге Джелена про сводные Формулы в целом С новыми формулами (LAMBDA, новые массивы), от начального до продвинутого уровня: главы про формулы в Microsoft Excel Inside Out. С новыми формулами посложнее: Advanced Excel Formulas: Unleashing Brilliance with Excel Formulas На русском с новыми формулами: главы про формулы у меня в "Магии таблиц" На русском до 2019 включительно от начального до продвинутого: главы про формулы в Excel 2019. Библия пользователя На русском до 2019 включительно посложнее: Мастер формул — Николай Павлов Старые формулы массива (до 2019 включительно) Ctrl+Shift+Enter Mastering Excel Array Formulas: Do the Impossible with Excel Formulas Thanks to Array Formula Magic — Girvin На русском: Мастер формул — Николай Павлов Новые формулы массива (динамические массивы) Up Up and Array!: Dynamic Array Formulas for Excel 365 and Beyond На русском: немного есть у меня в "Магии таблиц" Визуализация Визуализация данных при помощи дашбордов и отчетов в Excel — Куслейка если не обязательно именно в Excel, а нужна просто по визуализации, то эта: Основы визуализации данных. Пособие по эффективной и убедительной подаче информации — Уилке

Подписчики "Магии табличных формул" на Sponsr.ru не только получают доступ к видеоурокам и файлам с примерами (уже 15 уроков от 10 до 30+ минут — и новые видео каждую неделю), но и возможность посоветоваться по рабочим задач в личных сообщениях. А вот комментарий подписчицы — к видео "Формулы, ссылки и имена": Спасибо за подробный разбор. F2 и F3 стали настоящим открытием, хотя с Excel работаю достаточно давно👍 Присоединяйтесь! Еще есть места по 390 рублей в месяц https://sponsr.ru/excel_magic/subscribe/

Контрольное значение: отслеживаем значения ячеек в любом месте Если вам нужно всегда видеть, чему равно значение в какой-нибудь ячейке, даже если мы работаете на другом листе — добавьте эту ячейку в окно контрольного значения. Лента инструментов: Формулы — Окно контрольного значения Formulas — Watch Window В примере добавляем в Таблице в строке итогов сумму по всем заказанным товарам в окно контрольного значения, переходим на другой лист и меняем там цены некоторых товаров — и наблюдаем "в режиме онлайн", как меняется сумма заказа из-за изменения цен.

Несколько картинок из нового курса по сводным таблицам в Google Spreadsheets Напоминание: сегодня 16 апреля до конца дня его можно приобрести по первой цене со скидкой. Кому подойдет? Начинающим и продолжающим пользователям Google Таблиц, тем, кто добавляет Google Таблицы по работе к Excel или переходит туда. Кому не нужен? Если вы понимаете, как работает функция GETPIVOTDATA, умеете группировать данные в сводных по тексту/числам/датам, знаете, как применять рассчитываемые поля и как в них ссылаться на данные на листе и в исходных данных, а на завтрак на работе у вас обычно QUERY — курс вам не нужен :) Здесь подробная программа и оплата.Доступ сразу после оплаты и вечный. https://shagabutdinov.ru/pivot_google Я уверен в качестве материалов. Если вам не понравятся уроки, вы поймете, что не узнали вообще ничего нового и/или скажете, что качество видео/звука плохое — я верну вам 100% стоимости курса без вопросов в течение 2 недель после покупки.

У вас есть умная таблица. Это здорово! Ее, кстати, можно создать из диапазона сочетаниями клавиш Ctrl + T и Ctrl + L. А в умной таблице у вас есть строка итогов. Это тоже здорово. И для нее есть сочетание клавиш: Ctrl + Shift + T. И добавляет, и убирает строку итогов. Но вот проблема: без строки итогов можно просто "дописывать" данные к таблице — если вводить что-то в первой пустой строке под таблицей, новые строки автоматически станут ее частью. Но строка итогов всегда в самом низу таблицы, и если вводить данные под ней — ничего не произойдет, размеры таблицы не изменятся. Выход? Перемещаемся в последнюю строку с данными (не в строку итогов) и в последний столбец. Нажимаем Tab. Это самый быстрый способ добавить строку! Очень короткое видео без звука с демонстрацией.

Курс по сводным таблицам Google Spreadsheets Записал для вас курсец по сводным таблицам: от основ (зачем вообще сводные нужны и как построить сводную) до нюансов и деталей (как работают рассчитываемые поля, как правильно извлекать данные из сводной, как подготовить данные для сводной таблицы, в том числе сделать «анпивот» формулами, и многое другое). Говорим и про визуализацию, и форматирование. Так что у вас будет все необходимое для создания отчетов (в том числе для предварительной подготовки данных). Курс — это 20 видео с нормальным качеством и звуком, исходными и готовыми таблицами со всеми примерами, которые можно копировать и забирать себе для разбора или в качестве шаблонов. Доступ к курсу сразу после покупки. Доступ вечный. Первые три дня продаж со скидкой — курс будет стоить 2 900 ₽ до 16 апреля включительно. Далее цена вырастет. Если вам не понравятся уроки, вы поймете, что не узнали вообще ничего нового и/или скажете, что качество видео/звука плохое — я верну вам 100% стоимости курса без вопросов в течение 2 недель после покупки. Один из уроков мы выкладывали тут. Подробная программа, скриншоты с примерами и покупка — все по ссылке: https://shagabutdinov.ru/pivot_google

У вас в ячейке ссылка. И вы эту ячейку хотите выделить. Но при клике по ячейке происходит переход по ссылке! Открывается браузер. А-А-А-А! Спокойно: просто кликаем, но не отпускаем левую кнопку мыши и удерживаем секунду-другую. Ячейка будет выделена без перехода по ссылке. _ _ _ Курс "Магия новых функций Excel. Революция в табличных формулах"🔥

Тренинг "Табличные формулы для продолжающих"45801 24 мая-25 мая Вот в таком компьютерном классе пройдет интенсив по формулам ! Камерная обстановка и насыщенная интересная практика. 5 мест уже забронировано, так что осталось только 5 входных билетов с книгой "Магия таблиц" в подарок. Сегодня последний день, когда можно забронировать участие со скидкой 3 000 рублей. Присоединяйтесь! Если есть любые вопросы и сомнения - пишите: [email protected] Оставить заявку можно по этому же адресу или на странице тренинга: https://shagabutdinov.ru/formulas-offline Там же программа. В любом случае я немного поспрашиваю вас по поводу рабочих задач и вашего уровня перед бронированием места, чтобы понять, подойдет ли вам обучение.

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

Найти и заменить: меняем форматы, а не значения У вас есть много ячеек, разбросанных по листу/книге, с определенным набором параметров форматирования: допустим, голубая заливка, какое-то выравнивание, полужирное начертание и т.д. И вам нужно их все переформатировать по другому образцу. Допустим, без полужирного начертания. Вызываем окно "Найти и заменить" — Ctrl + H Выбираем справа Формат — Выбрать формат из ячейки Напротив поля "Найти" выбираем образец, какие ячейки будем менять А напротив "Заменить на" — выбираем образец, как они должны выглядеть Нажимаем "Заменить все". Готово! -- 💥Магия табличных формул — обучение по подписке. Всего 390 рублей / месяц для первых подписчиков!