Удаление источника данных сводной

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

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

А если вдруг захочется посмотреть на исходные данные, можно просто щелкнуть дважды на общий итог. Ведь двойной щелчок = создание листа со всеми строками, которые "стоят" за тем числом, на которое вы щелкнули в сводной. А общий итог складывается из всех строк исходных данных.
Так что если вы рассматривали вариант удаления источника, чтобы скрыть его от других пользователей и оставить им только сводную, увы, это не сработает 😺

Мышка или к...лавиатура?

Для перемещения в конец таблицы (диапазона) подойдет и то, и другое — выбирайте на ваш вкус:

Ctrl + стрелка — перемещение в конец (до последней заполненной ячейки) в направлении стрелки;
Двойной щелчок по границе ячейки — перемещение в соответствующем направлении (ловим курсор со стрелками во все стороны)

Любое из этих действий с нажатой клавишей Shift — и получите не просто перемещение, а выделение ячеек 😺

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

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

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

Ссылки с решеткой на динамические массивы

Вашему вниманию три минуты видео про новые ссылки с решеткой.
Так как в Excel 2021 / 365 появились динамические массивы (то есть формулу можно ввести в одну ячейку, а результат она выкатит размером более одной ячейки, да еще и размер этого результата может меняться в будущем) и соответствующие функции (например, УНИК / UNIQUE, СОРТ / SORT), то появился и новый тип ссылки — на ячейку с такой формулой.

Ведь если функция УНИК выдает уникальные значения, то откуда мы знаем, какого размера результат будет в будущем? Ведь уникальных значений в источнике может стать меньше или больше.
Поэтому мы не можем сослаться на результат стандартно, например как на A1:A10. Вдруг потом в 11 строке тоже будет результат выдачи?
Поэтому на такие формулы нужно ссылаться с решеткой A1#.
A1# — такая ссылка будет возвращать массив значений, который вычисляется формулой, находящейся в ячейке A1.

А вот ссылка на видео про старые и новые формулы массива:
https://t.me/lemur_excel/95

Чередование строк в сводной: пользовательский стиль

В сводных, как и в "обычных" (умных) таблицах можно включать чередование строк.
Но не всегда стандартный вариант вам подойдет.
Если нужен свой вариант (1 строка белая + 3 голубых, например) — создаем свой стиль сводной таблицы и там настраиваем размер и формат строк.

Вкладка "Конструктор" на ленте (контекстная — появится только при активации сводной) —> Стили сводной таблицы —> Создать стиль сводной таблицы
Design —> PivotTable Style Options —> New PivotTable Style

И далее "Первая полоса строк", "Вторая полоса строк" (First Row Stripe, Second Row Stripe).

Как найти все формулы в диапазоне?

Вариант первый, "на один раз":
"Найти и выделить" —> Формулы
Find and Select —> Formulas

Все ячейки с формулами в активном диапазона будут выделены.
А если активна одна ячейка — то все формулы на листе.

Как найти все формулы в диапазоне?

Вариант второй, динамический — через условное форматирование и функцию ЕФОРМУЛА / ISFORMULA, которая выдает ИСТИНА / TRUE, если в ячейке есть формула.
Условное форматирование — Создать правило — Использовать формулу...
Conditional Formatting — New Rule — Use a formula...

И вводим формулу:

=ЕФОРМУЛА(первая ячейка форматируемого диапазона)

И далее выбираем форматирование: как мы хотим оформлять ячейки, содержащие формулы.

Проводили недавно вебинар с Лемуром, и один из вопросов слушателей был такой: как фильтровать данные в сводной (или просто в диапазоне / таблице) по последним N дням месяца/квартала/года? Например, по последним трем?
Временная шкала в сводной таблице такого не умеет.

Здесь можно добавить к данным расчетный столбец, где формула будет выдавать одно из двух - последние три дня или нет. И потом фильтровать на основе этого столбца. Справа здесь скриншот с примером, а вот один из вариантов такой формулы:
=ЕСЛИ(И(A1>=КОНМЕСЯЦА(A1;0)-2;A1<=КОНМЕСЯЦА(A1;0));"Посл три дня месяца";"Другие дни")

Функция КОНМЕСЯЦА / EOMONTH выдает последнюю дату месяца. Первый аргумент — дата, а второй — отступ от нее по месяцам. То есть КОНМЕСЯЦА(A1; -1) вернет последнюю дату предыдущего месяца относительно даты в A1.
А с нулем это будет последняя дата этого месяца, того, к которому относится дата в первом аргументе — без сдвига.
И в формуле мы проверяем с помощью функции И / AND (проверяет одновременное выполнение всех условий), попадает ли наша дата в промежуток между датой за три дня до конца месяца и собственно концом месяца.

Друзья, наша с Лемуром книга тем временем доехала до всех основных книжных магазинов!

Внутри — все от основ и интерфейса до новых функций, появившихся в 2021-2023 годах (в том числе LAMBDA и ее вспомогательных функций) и визуализации данных. Все функции и команды на двух языках, есть информация о том, какие вещи доступны в Google Таблицах.

Если вам нужны полкило скриншотов из Excel и Google Таблиц (и только одна фотография кота) и 45 миллионов байт с примерами, заказывайте:
На сайте издательства (там же электрическая книга)
Book24
Лабиринт
Озон
Литрес (электрическая)

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

Нажимайте Ctrl + K (стандартное сочетание для вставки гиперссылки), выбирайте папку (Искать в) и далее нужный файл.

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

Нажимаем ОК и готово! В ячейке появится ссылка, при щелчке на которую будет открываться этот файл.

Еще один вопрос с недавнего вебинара:
Был столбец с пунктами типа 10.1, 10.2, 10.3...10.10, 10.11, формат текстовый... Поменял формат на числовой, после чего часть цифр превратилась в пятизначные типа 45430, 44438, часть в даты.

Как отметили наши коллеги из МИФа, для оптимиста стакан наполовину полон, а для Excel — первое февраля.
И вот тут как раз такой случай, нам нужна нумерация такого вида, которую Excel воспринимает как даты.
Чтобы ввести это как текст, ставьте апостроф в начале, который превратит значение в текстовое (в ячейке он отображаться не будет — на скриншоте это третья строка):

'10.1

А если нужно уже имеющиеся данные превратить в порядковые пункты, можно использовать такую формулу:
=ДЕНЬ(ячейка) & "." & МЕСЯЦ(ячейка)
За счет того, что мы склеиваем несколько значений в одну текстовую строку (с помощью амперсанда), результат будет текстом.

Оператор неявного пересечения @

Вряд ли вы будете использовать этот оператор в формулах в обычных диапазонах (за пределами "умных" таблиц), но можете с ним столкнуться.
Произойти это может, когда вы откроете книгу в новых версиях (Excel 2021 / 365) с формулами, сделанными в старых версиях.

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

В новых версиях ссылка на диапазон — это сразу ссылка на диапазон, а не на пересечение с ним. То есть
=A1:A10 — это сразу весь диапазон A1:A10, формула вернет результатом размером в 10 строк.
А
=@A1:A10 — это пересечение с диапазоном A1:A10, то есть одно значение из той же строки, в которой находится формула.

Следующая формула в новых версиях будет возвращать диапазон из 12 значений — разницу между парами значений в столбцах C и B:
=C2:C13-B2:B13

В старых версиях такая формула будет возвращать одно значение (для текущей строки).

P.S. Ну а в "умных"таблицах (чтобы создать такую, нажмите Ctrl + T) этот оператор используется в любой версии по умолчанию, но не с диапазоном, а с названием столбца в квадратных скобках. Например, [@План] — ссылка на столбец с заголовком "План" на эту же строку (ту, где формула).

Up Up and Array!: Dynamic Array Formulas for Excel 365 and Beyond

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

Речь про функции SORT, SORTBY, FILTER, UNIQUE, SEQUENCE и про функции для работы с массивами — RANDARRAY, TOCOL, TOROW, WRAPCOLS, WRAPROWS, VSTACK, HSTACK, CHOOSECOLS, CHOOSEROWS, EXPAND, TAKE и DROP, а также про текстовые функции TEXTSPLIT, TEXTBEFORE, TEXTAFTER.
Тут максимум деталей и нюансов (для новичков) и много примеров применения новых функций, в том числе их комбинаций (это будет интересно и новичкам, и продолжающим) — насколько же проще стало решать многие задачи!
Есть файлы с примерами.

Ну а полный обзор табличных книг вместе с этой новинкой — по постоянному адресу:
https://teletype.in/@renat_shagabutdinov/excellent_books

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

Общая логика формулы такая:
Берем первый кусочек, приклеиваем (&) к нему нужный символ, который повторяем много раз с помощью функции ПОВТОР / REPT, и потом справа приклеиваем (&) второй кусочек.
Сколько раз повторять символ (в нашем случае дефис)?
Берем какое-нибудь число, которое будет больше, чем наши текстовые фрагменты, и из него вычитаем число символов (ДЛСТР / LEN) в наших склеиваемых текстовых фрагментах. Таким образом получится, что число символов-заполнителей везде будет разное, но общее число символов одинаковое.

P.S. Магия сработает только с моноширинным шрифтом — например, с Courier New.

Тем временем книга получила статус "Хит" на Озоне и "Бестселлер", "Хит продаж" на Литресе!

А вот и первые отзывы (пока на Озоне):
отличная подача материала. намного дешевле курса!

Большое количество полезных фишек для ускорения работы

Отлична книга, великолепная подача, все «разжевано». Можно читать как подряд, так и местами, которые нужны. Пока это самое понятное пособие по Excel, которое я читала

Собрали для вас с Лемуром ссылки на основные магазины, где можно купить:

На сайте издательства
Book24
Лабиринт
Озон
Литрес (электрическая)
Wildberries

Если уже прочитали, напишите отзыв тоже, будем признательны 😺

Если вы хотите, чтобы в ячейках в диапазоне можно было вводить все слова только с заглавной буквы — например, если там хранятся ФИО — можно воспользоваться проверкой данных.

Формула будет выглядеть так:

=СОВПАД(первая ячейка диапазона; ПРОПНАЧ(первая ячейка диапазона))

Функция СОВПАД / EXACT проверяет совпадение с учетом регистра. А ПРОПНАЧ / PROPER меняет регистр текста — делает первые буквы каждого слова заглавными.
Таким образом, мы проверяем, совпадает ли текущее значение ячейки с ним же, но в "правильном" регистре.
И если не совпадает, то функция СОВПАД возвращает ЛОЖЬ / FALSE — и проверка данных ругается.

Если дата в ячейках записана как текстовое значение вида ДДММГГГГ, без точек/дефисов/других разделителей, можно превратить такой текст в настоящую дату формулой:

=ДАТА( ПРАВСИМВ( ячейка с датой; 4) ; ПСТР (ячейка; 3; 2) ; ЛЕВСИМВ (ячейка; 2) )

Функция ДАТА / DATE возвращает дату, заданную тремя параметрами — годом, месяцем и днем.
Ее аргументы мы получаем текстовыми функциями:
Год — извлекая первые цифры цифры с помощью ПРАВСИМВ / RIGHT (она возвращает первые N символов из текстовой строки)
Месяц — извлекая два символа, начиная с третьего, с помощью ПСТР / MID.
День — последние две цифры с помощью функции ЛЕВСИМВ / RIGHT.

Связанные выпадающие списки

Если вы хотите, чтобы список возможных значений в одном списке определялся на основе другого — можно использовать функцию ФИЛЬТР / FILTER для формирования списка — например, товаров только одной, выбранной категории.

И далее можно на эту формулу ссылаться из проверки данных (Data Validation) с помощью нового типа ссылок с решетками, о которых мы рассказывали здесь:
https://t.me/lemur_excel/165

Подробно — в коротком видео со звуком. Отдельным сообщением прикрепляем файл с формулой выше.

P.S. Радость эта будет доступна, увы, только в новом Excel.

Друзья, если вы работаете не только в Excel, но и в Google Таблицах, то подписывайтесь на одноименный, дружественный и вообще лучший канал по теме. К которому в комплекте идет чат, населенный настоящими профессионалами, готовыми помочь (при понятной формулировке задачи и наличии примера 😺)
https://t.me/google_sheets
@google_spreadsheets_chat

Вот на днях выложил там большую статью про функцию IMPORTRANGE, которая позволяет загружать данные из одной таблицы в другую:
Главная статья импорта