Магия Excel

Быстрое скрытие столбцов

Чтобы скрыть столбцы (выделенные столбцы или столбцы, относящиеся к выделенным ячейкам), нажмите Ctrl + 0
Сработает и для несмежных столбцов (если вы заранее выделите ячейки, зажав Ctrl).

А как показать скрытые столбцы?
Это сочетание Ctrl + Shift + 0. Но оно может не работать. И тогда вам придется чуть пошаманить в настройках Windows — см скриншот :)

Для строк: то же самое, но с девяткой. Ctrl + 9 и Ctrl + Shift + 9.

Книги по Excel — 2024

Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365)
Амазон

В этом году дочитывал Билла Джелена про макросы. Мощнейший автор, конечно. Умеет объяснять сложное. Хотя именно по макросам еще мне нравится старая книга Уокенбаха "Профессиональное программирование на VBA", хотя она по 2013 Excel, в VBA мало что изменилось. Но у Уокенбаха больше деталей и тем, а у Джелена потрясающее объяснение и некоторые скрытые фишки, а так же есть про новые возможности (как LAMBDA, Power Query). Кстати, он отмечает, что эти инструменты вполне себе заменяют в ряде случаев макросы.

А у Джелена еще серия книг по сводным таблицам по каждой версии и по Excel в целом. В том числе следующая книга.

MrExcel 2024: Igniting Excel
Амазон

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

И именно книги Джелена из этой серии Igniting Excel я бы назвал так, как названа следующая книга (скрытые сокровища Excel).

Exploring Microsoft Excel's Hidden Treasures
Амазон

Неплохо, но все-таки не так круто, и я от силы 3-4 штуки пометил для себя. Плюс не порадовало малое количество скриншотов. Так что не могу прямо-таки рекомендовать, если прочитано все, то можно посмотреть, но в качестве одной из первых смысла брать нет вообще.
Темы — всего понемногу, от условного форматирования и умных таблиц до Power Query и формул (вплоть до LAMBDA).
В комплекте файлы и цветные скриншоты в отдельном PDF.

The Definitive Guide to Power Query (M): Mastering complex data transformation with Power Query
Амазон
ДМК-Пресс (книга на русском)

Хотя я покупал электронку сразу после выхода, но когда появилось российское издание, заказал его в бумаге. В российском издании 750 страниц. Фундаментальная штука именно про язык M, а не Power Query, для тех, кто разобрался с интерфейсом и основными вещами там и хочет переходить на следующий уровень и разобраться с этим языком и творить магию в PQ. Тираж всего 200 экземпляров, не откладывайте!
Скриншоты в российском издании оригинальные, команды на двух языках, текст на русском, понятное дело :) Файлы и примеры есть на GitHub и на сайте издательства оригинала. И рисунки в цвете отдельно, как в предыдущей книге, но в российском издании они и так в цвете.

Основы визуализации данных
Озон
Это не совсем про Excel, но книга очень понравилась, так что ее тоже добавлю. Хотя не все в ней можно построить в Excel и она не завязана на какое-то приложение, но многое можно и вообще полезно. Примеров, идей и правил много, полезно. Книга цветная (у O'Reilly, напомню, это можно понять по тому, цветной ли зверь на обложке).

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

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

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

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

_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥

🚀 Откройте новые горизонты с Excel!

Авторский канал EasyExcel — ваш надежный инструмент для освоения всех возможностей Excel.
Изучайте практичные фишки и секретные приемы, которые сэкономят ваше время и оптимизируют рабочие процессы.

⭐ Секреты в Excel и Google-Таблицах
⭐ Продвинутые формулы
⭐ Автоматизация задач на VBA

Все доступные знания от сертифицированного специалиста по Microsoft Excel.

✅ Присоединяйтесь к @myeasyexcel и получите мгновенный доступ к ценным материалам

Реклама. Erid: 2Vtzqvdu3zJ

Сортируем данные внутри текстовой строки

Это один из многочисленных примеров курса "Магия новых функций Excel. Революция в табличных формулах" 🔥

У нас есть текст (в одной ячейке) вида "Лемур 87,Штрудель 47,Вишенка 92". Нам надо получить "Вишенка 92,Лемур 87,Штрудель 47", то есть отсортировать и снова получить одно текстовое значение, а не таблицу.

С новыми функциями все это можно проделать формулой.
Сначала превратим текст в таблицу с помощью ТЕКСТРАЗД / TEXTSPLIT — для этого зададим два разделителя, запятую для строк и пробел для столбцов:

=ТЕКСТРАЗД(A1;" ";",")

Потом отсортируем с помощью функции СОРТ / SORT:

=СОРТ(ТЕКСТРАЗД(A1;" ";",");2;-1)

Полученную таблицу, уже упорядоченную, обработаем функцией BYROW — значения из каждой строки будем объединять функцией ОБЪЕДИНИТЬ / TEXTJOIN:

=BYROW(СОРТ(ТЕКСТРАЗД(A1;" ";",");2;-1);LAMBDA(стр; ОБЪЕДИНИТЬ(" ";;стр)))

Получим уже одномерный массив (столбец). И его объединим в одну текстовую строку с помощью еще одной функции ОБЪЕДИНИТЬ, только теперь уже с другим разделителем (запятой):

=ОБЪЕДИНИТЬ(",";;BYROW(СОРТ(ТЕКСТРАЗД(A1;" ";",");2;-1);LAMBDA(стр; ОБЪЕДИНИТЬ(" ";;стр))))

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

Для такой задачи нужен макрос и он не очень сложный – главная идея в том, что мы в таблице в заголовках придумываем какие-то заглушки, которые подставляем и в документ. Это может быть что угодно, мы будем это заменять макросом (как делали бы вручную через Ctrl + H) на конкретные значения.
Заглушка может выглядеть в духе {СНИЛС}.

Один внешний цикл: по всем строкам таблицы, то есть по каждому договору, который нужно заменить. В нем создаем документ по шаблону и сохраняем под своим именем.
И далее внутренний цикл – по каждому столбцу в этой строке. Заменяем то, что в заголовке, на то, что в строке в этом столбце. То есть заглушку на значение.
Это циклы по всем строкам и столбцам. То есть можно добавлять новые поля (заглушки), строк (будущих договоров) тоже может быть сколько угодно.

Скачать файл с модулем (кодом макроса) можно по ссылке.
Чтобы добавить его к себе в книгу Excel, нажмите в ней Alt+F11 и в редакторе VBA щелкните правой кнопкой в Project Explorer, выбрав «Import File».

В видео со звуком – краткий обзор макроса и демонстрация работы.

Вычисляем отступ в ячейке

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

А там понадобилось фильтровать и убирать строки не по каким-то значимым признакам, а только по форматированию, причем по отступу (есть такие кнопки — "Увеличить отступ" и "Уменьшить" на вкладке "Главная").
А когда-то давно клиент просил переделывать выгрузку из 1С, где нужно было данные с разными отступами, которые были в одном столбце, раскидывать по разным столбцам.

Тут понадобится VBA, но в случае с вычислением отступа будет одна строчка кода.

Открываем редактор VBA — Alt + F11
Вставляем новый модуль в текущую книгу, если задача "на один раз".
Если функция нужна будет в разных файлах: создаем новую книгу, вставляем модуль там, сохраняем как надстройку Excel (формат .XLAM) и добавляем эту надстройку в Параметрах (Параметры — Надстройки — Надстройки Excel).

В модуле в любом случае вводим такой код:

Function ОТСТУП(cell As Range)
    ОТСТУП = cell.IndentLevel
End Function

Мы даем функции в качестве аргумента ячейку, а возвращает она значение отступа (IndentLevel) этой ячейки.

Кнопки группировки можно скрывать!

У вас на листе группировка. Она нужна, но кнопки + / - занимают слишком много места.

Нажмите Ctrl + 8, и они исчезнут. Текущая группировка никуда не денется, она сохранится. Менять ее не получится, но достаточно нажать Ctrl + 8, чтобы вернуть кнопки и менять группировку опять.

А для быстрой группировки используйте горячие клавиши:
Alt + Shift + → (группировать)
Alt + Shift + ← (разгруппировать)

_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥

Вставка текущих даты и времени в ячейки

Если нужно статично (вставили и значение осталось неизменным):

Если при открытии Excel у вас была русская раскладка:
Ctrl + Shift + 4 для даты
Ctrl + Shift + 6 для времени
Ctrl + Shift + 4 + пробел + Ctrl + Shift + 6 для даты и времени в одной ячейке

Если была английская:
Ctrl + ; для даты
Ctrl + Shift + ; для времени
Ctrl + ;+ пробел + Ctrl + Shift + ; для даты и времени в одной ячейке

_ _ _

Если нужно с обновлением:
Функция СЕГОДНЯ() / TODAY() для даты.

Функция ТДАТА() / NOW() для даты и времени.
У обеих функций нет аргументов. Скобки нужны, чтобы Excel понял, что это функция, а не имя, и не возвращал ошибку. Но аргументов не будет. Не можем мы с вами повлиять на ход времени и изменить в этом деле какие-то параметры :)

_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥

Друзья, вашему вниманию статья про функции GROUPBY и PIVOTBY: про каждый аргумент функций.

С ними можно даже сделать сводную таблицу с текстом в области значений (разбираем в статье, как)! Можно сортировать, фильтровать, группировать данные, отключать и включать итоги и так далее.
К статье приложен файл с примерами.

https://shagabutdinov.ru/tpost/zjc1ca6uy1-funktsii-grupppo-groupby-i-svodpo-pivotb

Напоминаю, эти и многие другие новые и сверхновые функции — в моем новом курсе "Магия новых функций Excel. Массивы, регулярные выражения и многое другое".

А про такую диаграмму уже завтра придет письмо всем, кто подписался на рассылку 😸
И заодно про таблично/личные новости.

Подписаться можно тут:
https://shagabutdinov.ru/#subscription

Ура, курс "Визуализируй это" уже доступен!

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

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

После покупки все уроки и шаблоны будут доступны сразу:
https://www.mann-ivanov-ferber.ru/courses/visualization/

Подбор параметра (Goal Seek) находится в коллекции «Анализ “Что если”» (What-If Analysis) на вкладке ленты «Данные».

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

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

_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥

Нарастающий итог: закрепляем только первую ячейку в диапазоне

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

1. Ссылаемся на первую ячейку, эфчетырим ее (то есть нажимаем F4, чтобы сделать ссылку абсолютной, "закрепить")

2. Вводим двоеточие и ту же самую ячейку, но уже оставляем относительной. Получается диапазон с началом и концов в одной ячейке, но конец не закреплен - так что при протягивании/копировании формулы будет меняться.
=СУММ($B$2:B2)

3. Протягиваем и получаем диапазон с началом в одной и той же ячейке и концом в текущей строке.

_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥

Как спрятать формулы?

Итак, у вас секретнейший алгоритм расчета чего-то там.

Выделяем ячейки, заходим в окно формата (Ctrl + 1) и там включаем соответствующий флажок — "Скрыть формулы".

После чего нужно защитить лист (с паролем, если вы настроены очень серьезно, хотя пароль и не обязателен).

И формулы не будет видно. Но они будут вычисляться, разумеется.

И если мы отключим защиту ячеек, влияющих на формулу (как в видео столбец F "Сумма"), то можно будет менять их значения даже при защите листа и формулы будут пересчитываться. Но мы их все равно не увидим 😸

Как отключить защиту отдельных ячеек? По умолчанию все ячейки защищаемые. Можно снять защиту для отдельных ячеек — там же, где и скрытие формул, в окне формата на вкладке "Защита".

_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥

Друзья, а на любой странице (внизу в конце страницы) сайта https://shagabutdinov.ru/
можно подписаться на мою почтовую рассылку — в ней будут табличные новости, табличная же польза, и иногда немного личного. Не чаще 1 раза в 1-2 недели.

https://shagabutdinov.ru/#subscription

Первый выпуск отправится уже вот-вот! В нем — про графический слой Excel, подключение среза к нескольким сводным и про российское издание книги по языку M.

Нумеруем только видимые строки

Если мы хотим, чтобы при скрытии строк номера пунктов обновлялись автоматически (то есть нумеровались видимые в моменте строки, а не все, что есть на листе) — нужно использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL.

Ибо она умеет обрабатывать только видимые (не скрытые) ячейки.

В нашем случае мы считаем значения, а не суммируем или что-то еще, то есть нужна функция СЧЁТЗ / COUNTA. Внутри SUBTOTAL ее код — 103 (у SUBTOTAL тип вычисления указывается в первом аргументе).

А что считаем? Для каждой строки мы считаем, сколько значений есть в видимых строках с начала таблицы (у нас это B2) до текущей строки.

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

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;$B$2:B2)

$B$2:B2 — начало всегда в B2, а конец диапазона в строке с формулой. Такую ссылку не задать через F4, придется попотеть и ввести доллары вручную.

_ _ _
Ну а заодно напоминание про 🔥 клавиши:
Ctrl + 9 — скрыть строку
Ctrl + Shift + 9 — отобразить скрытые строки

_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥

Откройте в себе внимательного аналитика и научитесь превращать обычные таблицы в мощный инструмент для анализа и автоматизации на бесплатном курсе-симуляторе «Excel: простые шаги для оптимизации работы с данными».

Курс подойдёт новичкам и тем, кто уже работает с данными: финансистам, маркетологам и продактам. Вы узнаете, как с помощью формул, сводных таблиц и прочих фишек Excel сделать свою работу проще и эффективнее.

🎁 После бесплатного обучения вы получите в подарок карьерную консультацию и скидку до 50% на курс-профессию "Аналитик данных", чтобы улучшить свои навыки под руководством экспертов.

Присоединяйтесь бесплатно в любое время и убедитесь, что Excel — это не просто таблички.

Реклама. ООО "Нетология". Erid 2VSb5xnHjVJ

Аргументами функции ВЫБОР / CHOOSE могут и... другие функции!

Можно сделать выпадающий список, в котором будет несколько вариантов вычислений.
В нашем простом примере — в ячейке M8 список (проверка данных) из диапазона M4:M6 — там среднее, сумма и количество.

Функция ПОИСКПОЗ / MATCH подскажет нам, каким по порядку в списке идет выбранный тип вычисления. Полученное число будет первым аргументом функции ВЫБОР. А последующие аргументы — функции.

Например, если пользователь выбрал "сумму", ПОИСКПОЗ будет возвращать 2. А значит, ВЫБОР выдаст второй элемент из списка. Это функция СУММ. Получается, что мы выбираем, какую формулу вычислять на основе значения из выпадающего списка. Простой вариант добавления интерактива в отчеты!

_ _ _
Мини-курс "Магия новых функций Excel. Революция в табличных формулах" 🔥

⏺Устали от постоянных изменений в налоговом законодательстве?

⏺Запутались в тонкостях
бухгалтерского учета?

⏺Хотите быть в курсе актуальной и проверенной информации?

Если вы бухгалтер, налоговый консультант, предприниматель или руководитель компании

🔥У нас для вас есть хорошая новость!

👉 Учебный центр "Что делать Квалификация" запустил
полезный телеграм-канал.

В канале вы найдёте:

✅Актуальную информацию о налоговых и бухгалтерских изменениях: будете в курсе важных поправок

✅Полезные рубрики с ответами на вопросы: задавайте вопросы и получайте ответы от сотрудников и лекторов Центра

✅Лайфхаки работы в 1С

✅Подробные бухгалтерские задачки с разборами

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

Теперь не нужно искать информацию по всему интернету!

👉 Подписывайтесь на канал
"Что делать Квалификация" и получайте всю необходимую информацию в одном месте
⤵️⤵️⤵️
https://t.me/+HUeahiQ3av1kZGVi

Реклама ООО "Что делать Квалификация", ИНН 7714349600, erid: 2VtzquqnUnz

Визуализируй это: курс по визуализации данных в Excel

Тут неоднократно спрашивали про этот курс, так что делюсь: уже можно приобретать, а первого декабря откроется доступ сразу ко всему:
— 20 видео
— к каждому видео исходные и готовые файлы Excel
— текстовые материалы
— 6 самостоятельных работ
— Excel и Google Таблица с шаблонами диаграмм, в том числе не из уроков: вдохновляйтесь или подставляйте сразу свои данные и используйте
— Обзор литературы

Курс записан именно в Excel. В текстовом формате будут дополнения: что доступно и как работает в Google Таблицах / российском Р7-Офис. На скриншотах несколько примеров того, что внутри. На странице курса можно посмотреть один из уроков.
И там же купить:
https://www.mann-ivanov-ferber.ru/courses/visualization/

Как заполнить пустые ячейки (вниз, то есть значениями сверху)?

Закрепляем верхнюю строку, если еще не)
Идем в конец диапазона (Ctrl+End или Ctrl+стрелка вниз по заполненному столбцу)

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

Нажимаем F5 и далее «Выделить» — «пустые ячейки»

Вводим формулу — нажимаем «равно» и на стрелку вверх
То есть ссылаемся на ячейку сверху (это R[-1]C в нотации R1C1 или =A2 в привычном стиле A1 — для формулы в ячейке A3)

И нажатием Ctrl+Enter вводим формулы во все выделенные ячейки (а это, вспомним, только пустые!)

То есть в каждой пустой теперь ссылаемся на ячейку сверху.

Дальше можно формулы скопировать и вставить как значения (Ctrl+C и Ctrl+Shift+V / Ctrl+Alt+V)

Алгоритм в коротком видео (без звука).

Мини-курс "Магия новых функций Excel" 🔥

Магия новых функций Excel: революция в формулах 🔥
Лемур напоминает: уже 18 ноября цена на новый курс вырастет до 2 300. Пока можно успеть купить за 890!

Внутри:
14 видео от 5 до 25 минут с качественным монтажом
Дополнительные текстовые материалы
Файлы со всеми примерами и формулами — исходные и готовые.

Покупать тут — и там же подробная программа и примеры:
https://shagabutdinov.ru/magic-excel

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

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

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

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

Мини-курс "Магия новых функций Excel" 🔥

📊 По данным исследований, около 25% всех финансистов России работают удалённо.

📈 Если вы бухгалтер, финансовый директор или финменеджер, хотите быть в курсе новейших тенденций и получать практические инструменты для работы, рекомендуем подписаться на телеграм-канал «Финансовый директор | Мастер CFO».

Это крупнейшее сообщество финансистов и бухгалтеров в России численностью более 14 000 человек!

📄 В их посте собраны 20 таблиц, которые необходимы каждому финансисту, описаны 7 проверенных способов найти клиентов, раскрыты 10 лайфхаков работы в 1С для бухгалтеров и показаны кейсы внедрения управленческого учета в различных сферах бизнеса.

Получите доступ к уникальным материалам от финансового директора со стажем 15 лет!

Скачать бесплатно.

Делаем кнопку группировки более наглядной

Функция SUBTOTAL / ПРОМЕЖУТОЧНЫЕ.ИТОГИ (подробнее про нее здесь) агрегирует только данные из видимых ячеек.
Соответственно, мы можем проверять, раскрыта ли ячейка строкой ниже — если применим функцию COUNTA / СЧЁТЗ, то есть подсчет любых значений. В SUBTOTAL это функция номер 3 (номер функции в первом аргументе).

И если строка скрыта, то функция вернет 0. Тогда можно выдать текст в духе "Подробнее" или "Раскрыть". Если не 0 — значит, строка ниже видима (и в ней что-то есть), выдаем другую надпись ("Скрыть").

=ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;A3)=0; "⬇️Подробнее"; "⬆️Скрыть")

Мини-курс "Магия новых функций Excel" 🔥

Точку данных в диаграмме (например, в линейчатой) можно заменить изображением

Для этого скопируйте изображение (Ctrl + C), выделите диаграмму, выделите нужный элемент (просто щелкните еще раз после выделения диаграммы на нужный элемент — вы поймете, что он выделен, когда круглые маркеры по углам останутся только у этого столбика).

И Ctrl + V — вставляем изображение.

После этого можно зайти в панель форматирования (Ctrl + 1), чтобы уменьшить боковой зазор между столбиками. Тогда они станут шире/выше.

И наконец, чтобы у вас не был один очень длинный кот или утка или что там у вас было на вставленной картинке, выберите в параметрах (Параметры ряда — Границы и заливка) вариант "размножить".

Мини-курс "Магия новых функций Excel" 🔥

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

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

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

Мини-курс "Магия новых функций Excel" 🔥

Магия новых функций Excel: революция в формулах 🔥

Друзья, мы с Лемуром рады анонсировать новый мини-курс, посвященный новым же функциям Excel (и — по большей части — Google Таблиц).

Это функции, которые могут решать задачи, ради которых раньше пришлось бы писать макросы или создавать запросы в Power Query!

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

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

В честь запуска новой версии сайта и этого курса — зверская цена от Лемура — 890 рублей! 🔥
Никаких уловок и вечных скидок не будет: 18 ноября цена вырастет до 2300 и больше уже никогда не вернется к старой.

Внутри:
14 видео от 5 до 25 минут с качественным монтажом
Дополнительные текстовые материалы
Файлы со всеми примерами и формулами — исходные и готовые.

Покупать тут — и там же подробная программа и примеры:
https://shagabutdinov.ru/magic-excel

Еще немного 🔥 клавиш. Что умеет F11?

Просто F11 — это вставка диаграммы. Но не простой, а диаграммы на отдельном листе (на котором не будет ячеек). Обычная диаграмма (внедренная) — это Alt + F1.

Alt + F11 — редактор VBA (макросов).

Ну а Shift + F11 — вставка нового листа.

Отображаем все скрытые строки сразу

1 Выделяем первую строку

2 Выделяем все строки вниз: Ctrl + Shift + ↓

3 Нажимаем Ctrl + Shift + 9

Готово🔥

Поиск и окно "Найти и заменить" в Excel и Google Таблицах

Казалось бы, все просто — нажимай Ctrl+F да ищи, нажимай Ctrl+H да заменяй/удаляй.
Но есть приятные опции:
— Можно искать/заменять в диапазоне/на листе/на всех листах
— Можно искать/заменять с учетом регистра
— В Google Таблицах в окне "Найти и заменить" можно использовать регулярные выражения (смотрите примеры в статье: можно поменять формат дат на другие или сделать еще какую-нибудь магию), а в Excel символы подстановки
— В Google Таблицах можно искать по ссылкам (а в обоих редакторах — по формулам);
— В Excel можно менять формат ячеек — достаточно выбрать образец для поиска и образец для замены.

Готовим с Лемуром новый курс — по новым же функциям Excel (365).

Вот такие и многие-многие другие интересности можно с этими новыми функциями делать.
Уже совсем скоро будет доступен!

Есть прекрасный сайт с производственным календарем для каждого года, начиная с 2014:
http://xmlcalendar.ru/?country=ru

Форматы там есть разные — например, XML (в этом формате у дат есть атрибут t с типом, и можно извлечь только праздничные (1) или только сокращенные рабочие дни (2) или рабочие СБ/ВС (тип 3)) или TXT, где просто список всех выходных и праздничных дней без типов.

Как получать данные? Можно разово вручную — скачать CSV и открыть в Excel или открыть ссылку с TXT-форматом, выделить все (Ctrl + A), скопировать (Ctrl + C) и вставить (Ctrl + V) в Excel.

Но так придется для каждого года вставлять данные вручную. Если вы хотите формулу, которая будет возвращать данные для текущего года, можно воспользоваться функциями ГОД / YEAR и СЕГОДНЯ / TODAY. Следующее сочетание будет возвращать номер текущего года:

ГОД(СЕГОДНЯ())

И его можно будет подставить в ссылку вместо значения конкретного года, так как ссылки меняются только на номер года:
http://xmlcalendar.ru/data/ru/2024/calendar.txt
http://xmlcalendar.ru/data/ru/2025/calendar.txt

Соответственно, ссылка на календарь текущего года в TXT-формате в формуле будет выглядеть так:

"http://xmlcalendar.ru/data/ru/" & ГОД(СЕГОДНЯ()) & "/calendar.txt"

А далее можно ее загрузить одной из функций. В Google Таблицах это IMPORTDATA:

=IMPORTDATA("http://xmlcalendar.ru/data/ru/" & YEAR(TODAY()) & "/calendar.txt")

В Excel (только на WIndows) это ВЕБСЛУЖБА / WEBSERVICE. Чтобы она не возвращала все даты "слипшимся" списком в одной ячейке, добавим функцию ТЕКСТРАЗД / TEXTSPLIT, чтобы разделить по переносу строку (символ с номером 10 — его нельзя напечатать, поэтому используем функцию СИМВОЛ / CHAR):

=ТЕКСТРАЗД(ВЕБСЛУЖБА("http://xmlcalendar.ru/data/ru/"&ГОД(СЕГОДНЯ()) &"/calendar.txt");;СИМВОЛ(10))

Но Excel не воспринимает как даты эти значения. Придется немного поколдовать: убрать непечатаемые символы через функцию ПЕЧСИМВ / CLEAN и сделать текст настоящей датой с помощью ДАТАЗНАЧ / DATEVALUE:

=ДАТАЗНАЧ(ПЕЧСИМВ(ТЕКСТРАЗД(ВЕБСЛУЖБА("http://xmlcalendar.ru/data/ru/"&ГОД(СЕГОДНЯ()) &"/calendar.txt");;СИМВОЛ(10))))

Горячие клавиши для быстрого перемещения и выделения в Excel🔥

Ctrl + PgDn/PgUp — следующий/предыдущий рабочий лист (а если открыто диалоговое окно, то перемещение между вкладками этого окна)

Ctrl + Backspace — возвращаемся к активной ячейке

Ctrl + A — выделяем всю текущую область (диапазон)

Shift + пробел — выделяем всю строку (если активна "умная таблица" — то выделяется столбец без заголовков — только данные — пределах таблицы, иначе — столбец в пределах всего листа)
Ctrl + пробел — выделяем весь столбец

Ctrl + стрелки — перемещаемся в конец диапазона (в направлении стрелки). Вместе с Shift — выделяем до конца диапазона.

Ctrl + End — перемещаемся в конец активной области на листе (в самые последние строку и столбец с данными)

P.S. Если делаете какое-то действие часто, на последнем шаге, когда кликаете на какую-то команду, остановитесь на секунду, наведите курсор на команду и посмотрите на подсказку — вполне вероятно, что там будет сочетание клавиш для нее.

Пробел: пересечение диапазонов

Немного экзотики. Пробел между диапазонами в Excel = пересечение этих диапазонов.

Например, такая формула:

=A1:A3 A2:C2 

Вернет значение из A2 — общей ячейки диапазонов A1:A3 и A2:C2.

Можно использовать и с именованными диапазонами — пример в видео.

=Магия_Excel_2 Март 

Кстати, чтобы быстро присвоить всем столбцам и/или строкам диапазона имена, можно использовать команду "Создать из выделенного" с вкладки "Формулы" (смотрите на видео) или нажать Ctrl + Shift + F3.

Считаем уникальные значения: Power Query

Наконец, четвертый вариант — если у вас есть Power Query, а это куда больше версий, чем в случае с новыми функциями.

Загружаем данные в Power Query:
Данные — Получить данные — Из таблицы / диапазона

Удаляем все, кроме двух столбцов — по одному будем группировать (у нас это "Клиент"), по другому считать уникальные значения (у нас это "Товар")

Преобразование — Группировать по — выбираем нужные столбцы и операцию "Количество уникальных строк"

Закрыть и загрузить в — выбираем "Таблица" на новый или существующий лист. Готово!

Считаем уникальные значения: сводная таблица

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

Но если в вашей версии Excel есть Power Pivot, то достаточно просто поставить галочку "Добавить эти данные в модель данных" при вставке сводной (а если PP нет, то и флажка этого не будет) — и случится магия. В списке вычислений появится "Число разных элементов", это и будут уникальные. Как все это сделать — в очень коротком видео без звука.

Залезать в сам Power Pivot, связывать там данные — все это не нужно. Собственно, связывать нечего, в данном примере мы строим сводную на основе одной таблицы. Но тот факт, что она будет добавлена в модель данных этой книги Excel (в Power Pivot), активирует эту опцию.

Ну а в Google Таблицах в сводных и так есть COUNTUNIQUE среди операций🔥

Считаем уникальные значения: старая школа

Как быть в старой версии Excel вплоть до 2019?

Воспользоваться формулами массива, суровыми — с фигурными скобками. Вводить такие нужно, напомним, сочетанием Ctrl+Shift+Enter (руками ввести фигурные скобки не получится).
Вот вариант формулы от Николая Павлова из его мощной книги "Мастер формул":

{=СУММ(1/СЧЁТЕСЛИ(тот же диапазон;тот же диапазон))}

Что тут происходит? Мы для каждого значения в диапазоне считаем, сколько раз оно встречается (функция СЧЁТЕСЛИ / COUNTIF). Допустим, некий клиент встречается 5 раз.
Потом делим единицу на эти числа. Для этого клиента в результате получим пять чисел 0,2. В сумме они всегда дадут единицу, то есть каждому клиенту (уникальному значению) будет соответствовать единица. И нам останется только просуммировать эти единицы, получив нужное число.

А подсчет уникальных с условием? Можно добавить вспомогательный столбец с такой формулой (по мотивам книги Майка Гирвина с очень говорящим названием Ctrl + Shift + Enter):

=СУММ(И(СЧЁТЕСЛИМН($B$2:B2;[@Товар];$A$2:A2;$H$6)=1;[@Клиент]=$H$6))

Ссылка вида $B$2:B2 означает, что мы в каждой строке ссылаемся на диапазон, начинающийся в B2 и заканчивающийся в текущей строке. То есть считаем от начала таблицы до строки, в которой находится формула.

Она будет возвращать единицу только для товаров выбранного клиента (это условие, клиент выбирается в списке в ячейке H6) — но только один раз, когда товар впервые встречается в списке. Для последующих вхождений она будет возвращать нули.
Для других клиентов нули будут априори ( не будет выполнено второе условие внутри СЧЁТЕСЛИМН / COUNTIFS. Нам останется просуммировать единицы во вспомогательном столбце.

Прикрепляем отдельно книгу Excel со всеми формулами — изучайте на здоровье!

Ух! Пожалуй, лучше использовать что-то попроще в старых версиях Excel. В следующем посте поговорим про сводные и Power Query.

Считаем уникальные значения: новые, модные и прогрессивные функции

У нас есть список сделок. Мы хотим понять, сколько у нас вообще клиентов (уникальных значений в столбце "Клиент") и сколько товаров покупал каждый клиент (не число сделок, то есть строк; не количество штук; а количество уникальных наименований).

Как решать эту задачу? Сегодня начнем с простого. Но простое доступно, увы, только в Excel 2021 / 365.

Список уникальных значений (или строк в общем случае) можно получить функцией УНИК / UNIQUE.

А потом посчитать, сколько в списке уникальных значений — старой доброй функцией СЧЁТЗ / COUNTA.

=СЧЁТЗ(УНИК(диапазон))

А если нужно количество уникальных товаров, купленных каждым клиентом?

Тогда сначала фильтруем одноименной функцией товары только по нужному клиенту, а потом уже считаем, сколько в этом списке уникальных значений:

=СЧЁТЗ(УНИК(ФИЛЬТР(диапазон с товарами;диапазон с условием=условие)))

В следующих постах обсудим другие варианты решения задачи!

P.S. А в Google Таблицах все можно сделать одной функцией COUNTUNIQUEIFS, считающей уникальные значения с одним или несколькими условиями.

Фокусировка ячейки — пустячок, а приятное новшество!

Раньше такое делали через макросы и формулы условного форматирования. Или только макросами. Или надстройкой. В любом случае без макросов решение было невозможно.

Теперь просто кнопка. Да еще и цвет можно выбирать🔥

Пока в бета-канале обновлений. Ждем, когда довезут до всех пользователей 365.

Вид — Фокусировка ячейки
View — Focus Cell

Декартово произведение (все комбинации значений) в Power Query

Итак, если у вас не новая версия Excel с супергуперпупер функциями с красивыми названиями вроде ПОСТОЛБЦ, придется идти в Power Query:

1 Делаем списки таблицами (Ctrl + T) и добавляем в PQ (Данные — Из таблицы/диапазона). После добавления первого загружаем его только как подключение, чтобы список отдельно не выгружался на лист. Добавляем второй и далее уже остаемся в редакторе PQ

2 Добавляем столбец в одном из запросов (списков). Добавление столбца — Настраиваемый столбец.

В нем просто ссылаемся на другой список (=Таблица1, например)

3 Теперь напротив каждого значения из текущего списка — таблица со всеми значениями из второго списка. Раскроем ее, нажав на кнопку со стрелками в заголовке — получим для каждого значения из первого списка столько строк, сколько есть во втором.

4 Останется объединить два столбца, выбрав в качестве разделителя пробел.

5 И выгрузить это дело в виде таблицы на лист Excel. При добавлении новых значений достаточно будет обновить запрос (Alt+F5) или правой кнопкой мыши — Обновить.

Декартово произведение (все комбинации значений) формулой

С новыми функциями можно и формулой (а без них — через Power Query, о чем будет в отдельном посте).

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

Второй список сделаем строкой с помощью ПОСТРОК / TOROW.

Потом склеим их амперсандом (&), добавив пробел. Получим то, что вы видите на скриншоте справа в столбцах F-I (произведение, а точнее, конкатенация в данном случае, строки на столбец дает прямоугольный диапазон).

Останется сделать его плоским списком — снова с помощью ПОСТОЛБЦ.

=ПОСТОЛБЦ(ПОСТОЛБЦ(первый список;1)&" "&ПОСТРОК(второй список;1))

Случайность в квадрате: как визуализировать вероятность

Если мы хотим наглядно показать, что что-то будет происходить примерно в 10%, 20% или N% случаев, можно поступить так:

1. Сгенерировать случайные числа в каком-то интервале, например от 1 до 100
В Excel 365 нам поможет функция СЛМАССИВ / RANDARRAY, в старых версиях СЛЧИС / RAND
(число от 0 до 1) или СЛУЧМЕЖДУ / RANDBETWEEN (целое число в заданном интервале)

Одна формула для нового Excel:

=СЛМАССИВ(число строк; число столбцов; 1; 100)

Отдельная формула, которую нужно вставить в каждую ячейку — для старых версий:

=СЛУЧМЕЖДУ(1;100)

2. Оставить в ячейках какой-нибудь знак, допустим, единицу, для тех случаев, когда случайное число меньше N, допустим, 10:

=ЕСЛИ(СЛМАССИВ(число строк; число столбцов; 1; 100)<=10; 1; "")

=ЕСЛИ(СЛУЧМЕЖДУ(1;100)<=10;1;"")

3 Сделать правило условного форматирования — для ячеек с единицей применять заливку какого-то цвета и шрифт того же цвета (чтобы скрыть единицы). Готово!
Для большей красоты можно добавить границу вокруг всего диапазона, убрать сетку на листе — на что хватит фантазии.

Весь процесс в коротком видео без звука.

Если при создании сводной таблицы вы включите флажок "Добавить эти данные в модель данных" (Add this data to Data Model), то впоследствии можно будет превратить сводную таблицу в формулы.

Что это за формулы такие? Формулы куба. Они могут напомнить вам функцию ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA), но она получает данные из существующей сводной. А функции куба заменяют собой сводную! И проще всего получить их в готовом виде, чтобы изучить, преобразовав существующую сводную таблицу.

Строим сводную таблицу — включаем флажок "Добавить эти данные в модель данных" — переходим на вкладку "Анализ сводной таблицы" и там выбираем "Средства OLAP" — Преобразовать в формулы" (OLAP tools — Convert to Formulas).

Что изменится? Каждая ячейка сводной станет независимой формулой. Больше не будет единого объекта — сводной таблицы — будут отдельные формулы. Вы сможете переупорядочить вашу сводную как вам захочется (см пример на видео без звука).

Какие это функции?
КУБЗНАЧЕНИЕ / CUBEVALUE — это как область значений сводной, в ней задается подключение (к модели данных, первый аргумент = "ThisWorkbookDataModel") и ссылки на заголовки столбца, названия элементов (в нашем примере категория ресторана из области строк), может быть ссылка и на срез.
Заголовки в сводной (и заголовок области значений в духе "Сумма по полю ...", и названия элементов в строках/столбцах) будут заданы другой функцией — КУБЭЛЕМЕНТ / CUBEMEMBER.
Еще и другие функции куборв, например, КУБМНОЖ / CUBESET (возвращает список всех значений из столбца) и КУБПОРЭЛЕМЕНТ / CUBERANKEDMEMBER, которая из ячейки с функцией КУБМНОЖ может извлечь значение по его порядковому номеру.

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

И Excel такое дело помечает ошибкой с зеленым треугольником — "Число сохранено как текст".

Эти ошибки можно отключить. Отправляемся сюда:
Параметры Excel — Формулы — Правила проверки ошибок — отключаем "Числа, отформатированные как текст или с предшествующим апострофом".

Excel Options — Formulas — Error Checking Rules — Numbers Formatted As Text of Preceded By An Apostrophe

Получаем название листа формулой

Функция ЯЧЕЙКА / CELL может выдавать разную информацию: например, полное имя файла (книги) вместе с листом. Для этого ее первый и единственный обязательный аргумент должен быть равен "имяфайла" ("filename").

А дальше — дело техники — вытаскиваем только имя листа текстовыми функциями.
В новом Excel совсем удобно: ТЕКСТПОСЛЕ / TEXTAFTER вытащит все, что после квадратной скобки.

=ТЕКСТПОСЛЕ(ЯЧЕЙКА("имяфайла");"]")

В старых версиях Excel воспользуемся комбинацией функций:
НАЙТИ / FIND подскажет, на какой позиции находится скобка, ДЛСТР / LEN — сколько в имени вообще символов — исходя из этого поймем, какая длина названия листа — в нашем случае 5 символов — именно столько извлечем с конца текстовой строки с помощью функции ПРАВСИМВ / RIGHT.

=ПРАВСИМВ(ЯЧЕЙКА("имяфайла");ДЛСТР(...)-НАЙТИ("]";...))

О работе финансовым директором глазами Софьи: что на самом деле нужно уметь, чтобы стать финансовым директором? Так ли сложно вникнуть в управленческий учет и автоматизировать этот процесс?

Софья автор канала Мастер CFO, знает ответ на этот вопрос. А ещё она бесплатно выкладывает автоматизированные таблицы Google, Ecxel, которые упростят жизнь любому, раскрывает секреты работы в 1С и разбирает случаи из практики.

Рекомендуем к прочтению и сохранению:

→ 8 телеграмм-каналов по поиску работы для финансиста.

→ Авторская методичка по анализу трех ключевых управленческих отчётов: ОДДС, ОПиУ и Баланс.
(Старая цена: 10 000 руб Новая цена: 0 руб.)

→ 10 лайфхаков работы в 1С, которые упростят жизнь любому бухгалтеру и позволят сэкономить сотни часов рабочего времени

→ 20 таблиц для начинающего финансиста для входа в управленческий учет.

→ Инструкция по автоматизации финансового учета.

И немного профессионального юмора ⬇️

«Как вы с такой точностью выполняете планы? А мы просто неправильно считаем 😀»

Присоединяйтесь, у нас интересно ✅ @findir_pro

Ссылки на несколько листов в формулах 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 = закрытие книги.