Магия Excel

Ссылки на несколько листов в формулах Excel Общий вид ссылки на несколько листов (это ссылка на листы от первого и до последнего по ярлыкам слева направо; если порядок листов в книге изменится, ссылка в формуле не поменяется): 'Первый лист:Последний лист'!Диапазон Следующая формула суммирует числа из ячеек B2 на листах от "$ счет" и до "Счет в юанях" включительно: =СУММ('$ счет:Счет в юанях'!B2) В названиях листов можно использовать символ подстановки — звездочку. Если в книге много листов со словом "Расходы" в названии ("Расходы январь", "Расходы февраль", . . . ). Следующая формула позволит просуммировать ячейки A1 со всех этих листов: =СУММ('Расходы*'!A1) Правда, в отличие от ссылки с двоеточием, звездочка в формуле не сохранится - после ввода такой формулы ссылка на лист со звездочкой превратится в формулу с отдельными ссылками: =СУММ('Расходы январь'!A1;'Расходы февраль'!A1;'Расходы март'!A1;...)

Обязательно ли показывать структуру круговой диаграммой? Она не самая наглядная и удобная для восприятия. Если нам важнее сравнить значения друг с другом, можно использовать линейчатую. А если все же хочется показать доли каждого значения, можно отразить их в подписях данных вместо абсолютных значений. Для этого рассчитаем доли в отдельном столбце (поделим каждое значение на общую сумму) и далее в параметрах подписей используем вариант "значения из ячеек". Он, увы, доступен не во всех версиях Excel 😿 Можно, конечно, править подписи вручную и вставить доли в старой версии таким образом, но это будет немного грустно (и совсем без обновления).

Гильдия магов Excel Еще несколько бесплатных видео вашему вниманию — уже более длинных 🤠 Потому что это вебинары "Гильдии магов Excel" в МИФе. Примерно 1 час по каждой из следующих тем: Колдуем с текстом в Excel. Регистр, пробелы, переносы, форматы Интерфейс и быстрый ввод данных. Лента инструментов, панель быстрого доступа Формулы Excel для новичков. Разбираемся со ссылками, знаками и функциями. Новые функции и инструменты 2021-2023. Что появилось в Excel 2021-2023 1 Заходим по ссылке: https://www.mann-ivanov-ferber.ru/courses/guild-excel 2 Выбираем интересующую тему, нажимаем "Получить запись" 3 Вводим почту 4 Получаем ссылку и смотрим (без VPN)! Приятного просмотра.

Друзья, залили на Kinescope наши видео. Изучайте на здоровье без регистрации, СМС, ВПН и других аббревиатур: Автоматическая нумерация списков в Excel Новые функции Excel: GROUPBY / ГРУПППО и PIVOTBY / СВОДПО. Агрегируем данные разными способами Флажки в Excel: в ячейках (новые) и элементы управления (в старых версиях) Объединяем умные таблицы в одну: формулы и Power Query Интерфейс Excel: приемы и горячие клавиши для ускорения работы Абсолютные и относительные ссылки в Excel. Стиль ссылок R1C1 Новая функция REGEXEXTRACT в Excel: извлекаем электронную почту, даты и другие фрагменты из текста Выпадающие списки в Excel с автоматическим добавлением новых значений

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

Типы данных Может ли в одной ячейке быть несколько значений? Может, если это типы данных (data types). Например, если вы введете в ячейки названия стран (на английском даже при русском интерфейсе Excel) и преобразуете их в тип данных "География" (Данные — Типы данных — География, Data — Data Types — Geography), то из таких ячеек можно будет извлекать данные формулой. Ссылаемся на ячейку с названием страны, ставим точку и видим варианты — что можно извлечь. Либо просто нажмите на смарт-тэг(иконку), которая появляется в ячейке со страной, и выберите нужные данные, формула сформируется автоматом в соседнем столбце. Уровней может быть несколько! Допустим, нам нужно население столиц: =A1.[Capital/Major City].Population А если бы мы хотели просуммировать население всех столиц стран из списка? =СУММ(A1:A3.[Capital/Major City].Population) Еще есть типы данных "акции", "валюты", в студенческой версии 365 еще и Wolfram (справочная система). А свои типы данных можно создавать с помощью Power Query. Типы данных работают в 365 и Excel Online.

И снова в личные сообщения пришел вопрос: как удалить пробелы из выгрузки? Числа с пробелами (и поэтому это де-факто текст, который не обработать нормально), но "Найти и заменить" (Ctrl + H) не помогает. Проблема в том, что пробелы бывают разные 🤯 Короткий ответ: пробел лучше копировать, то есть брать именно тот пробел, что есть в выгрузке, который нужно удалить из чисел. А не вводить с клавиатуры. В видео разбираемся более детально: как с помощью функции КОДСИМВ / CODE понять, что за символ вообще перед нами — в данном случае это не обычный пробел, а неразрывный, поэтому его не удалишь вводом обычного пробела с клавиатуры в окне "Найти и заменить". Его нужно либо копировать из данных и вставлять в окно "Найти и заменить", либо вводить с помощью кода — Alt+0160. И делаем макрос, который позволит удалять ненужные символы одним нажатием кнопки или сочетанием клавиш Alt + цифра. Код макроса можно сократить до такого: Selection.Replace What:=" ", Replacement:="" На месте пробела может быть другой символ, который вам нужно удалять в выделенном диапазоне. Не забывайте: действие макросов отменить через Ctrl+Z нельзя! Всю задачу разбираем в видео со звуком.

Начал готовить курс по визуализации данных в Excel/Google Таблицах Традиционно прикупил свежее по теме (две книги на переднем плане) и смотрю старое, тут не все, конечно, многое в электронке (тут подробнее про многие книги) Прочитал уже первую из них «Графики, которые убеждают всех» Александра Богачева и могу рекомендовать тем, кто только входит в тему — кратко, наглядно, хорошие примеры (из настоящих отчетов/презентаций в то числе!). Есть одна опечатка (пример про цвета, а диаграмма ч/б), но это даже не капля дегтя, а молекулы. Не специфично для Excel, хотя иногда есть уточнения, в том числе в классификации диаграмм автор уточняет, что есть там (правда, не уточняя версии Excel; диаграммы там постоянно добавляются). Если читали что-то еще хорошее по теме, порекомендуйте, пожалуйста.

Написали в РБК с Лемуром про несколько свежих задач, когда даже не самые сложные формулы и манипуляции помогают экономить очень много рабочих часов. Особенно там, где объемы большие, а до этого работали в ручном или почти ручном режиме! https://companies.rbc.ru/news/ylp76KL1rl/kak-tablitsyi-ekonomyat-kompaniyam-sotni-rabochih-chasov/

Вытаскиваем из даты всякое разное: подборка функций и формул Нужно получить номер квартала или посчитать число пятниц в периоде? Получить начало и конец месяца для заданной даты? Ловите пачку полезных формул для работы с датами в 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(дата)

Диаграмма "Карта" в Excel Если у вас есть данные по странам (и есть Excel 2019 / 2021 😸) — можно построить диаграмму "Картограмма": Вставка — Карты — Картограмма Insert — Maps — Filled Map Но настроек тут немного. Можно отображать названия стран, менять цвета заливки (2 или 3 цвета), включать и выключать отображение стран, для которых данных нет. В общем, не сравнить по возможностям с Power Map (3D-картами, которые можно строить на основе модели данных Power Pivot). Но для простых задач может хватить.

Задача: посчитать стоимость (то есть перемножить цену и количество) с условием (то есть не по всем подряд строкам) Если бы просто перемножить два столбца — цена и остатки — то все просто. Берем функцию СУММПРОИЗВ / SUMPRODUCT — она перемножает значения из нескольких массивов, а потом суммирует полученные произведения: =СУММПРОИЗВ(Прайс[Цена];Прайс[Остатки]) Но нам нужно не все подряд, а, допустим, только строки, в которых встречается определенный бренд — например, Orijen. Тогда добавим третий аргумент (массив) в функцию. С помощью функции НАЙТИ / FIND будем определять, есть ли искомый бренд в столбце "Название". Если функция выдаст ошибку (проверим это с помощью ЕОШИБКА / ISERROR), значит, бренда нет, а нам нужно, чтобы ошибки не было — так что мы будем превращать ИСТИНА (=ошибка есть, название не найдено) в ЛОЖЬ и наоборот. Таким образом, следующая конструкция выдаст ИСТИНА там, где искомое слово найдено: НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название]))) Но это будет массив из логических значений ИСТИНА и ЛОЖЬ, и мы превратим его в единицы и нули, умножив на -1 дважды: --НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название]))) Получится, что в нужных нам строках будут единицы, а в ненужных нули, и вся конструкция в целом вернет нам сумму произведений цены и количества только из нужных строк: =СУММПРОИЗВ(Прайс[Цена];Прайс[Остатки];--НЕ(ЕОШИБКА(НАЙТИ("Orijen";Прайс[Название]))))