Магия Excel

Горячие клавиши по понедельникам🔥 Сегодня -- Alt со стрелкой вниз Тут будет два варианта: 1 если вы в обычной ячейке в столбце с текстовыми значениями - в умной таблице или обычном диапазоне - получите раскрывающийся список со всеми значениями столбца. Можно увидеть, какие значения вообще в столбце встречаются и ввести нужное (либо стрелками на клавиатуре + Enter либо кликом мыши) Это не проверка данных, даже если ее нет в ячейках, этот раскрывающийся список работает. 2 если активна ячейка с заголовком и в таблице/диапазоне есть фильтр - откроется фильтр по этому столбцу. Ну а далее можно для выбора опций использовать стрелки на клавиатуре или подчеркнутые буквы (как и в других контекстных меню / диалоговых окнах Excel)

Отображаем все скрытые строки сразу 1 Выделяем первую строку 2 Выделяем все строки вниз: Ctrl + Shift + ↓ 3 Нажимаем Ctrl + Shift + 9 Готово🔥

Помогал коллегам: нужно формировать пачку документов Word по шаблону (в реальной жизни нужно было выбирать один из шаблонов под разные типы контрагентов, тут для примера упростим до одного шаблона), подставляя много данных из таблицы: реквизиты контрагента, всякую информацию о предмете договора – десятки столбцов. Подумал, что такое нужно многим и решил поделиться. Для такой задачи нужен макрос и он не очень сложный – главная идея в том, что мы в таблице в заголовках придумываем какие-то заглушки, которые подставляем и в документ. Это может быть что угодно, мы будем это заменять макросом (как делали бы вручную через Ctrl + H) на конкретные значения. Заглушка может выглядеть в духе {СНИЛС}. Один внешний цикл: по всем строкам таблицы, то есть по каждому договору, который нужно заменить. В нем создаем документ по шаблону и сохраняем под своим именем. И далее внутренний цикл – по каждому столбцу в этой строке. Заменяем то, что в заголовке, на то, что в строке в этом столбце. То есть заглушку на значение. Это циклы по всем строкам и столбцам. То есть можно добавлять новые поля (заглушки), строк (будущих договоров) тоже может быть сколько угодно. Скачать файл с модулем (кодом макроса) можно по ссылке. Чтобы добавить его к себе в книгу Excel, нажмите в ней Alt+F11 и в редакторе VBA щелкните правой кнопкой в Project Explorer, выбрав «Import File». В видео со звуком – краткий обзор макроса и демонстрация работы: https://kinescope.io/vh8VXVixsvhwYYCKSoPm73

Горячие клавиши по понедельникам 🔥 Вставляем текущие дату и / или время: все зависит от раскладки, которая была активна до включения Excel. Сочетаниями клавиш можно вставить дату и время как значения, они обновляться не будут. С обновлением — нужны функции, ТДАТА / NOW для даты-времени или СЕГОДНЯ / TODAY только для даты. Подробнее про дату и время читайте в статье: https://shagabutdinov.ru/date_time

Фильтр по значению ячейки Эта опция работает не только внутри диапазона, в котором уже стоит фильтр. Но можно и так: дописываете числовое условие вида ">15000" в первой пустой ячейке под столбцом с числовыми данными. Правая кнопка — Фильтр — Фильтр по значению выделенной ячейки (Filter — By Selected Cells Value). И фильтр установится, и данные сразу отфильтруются по этому столбцу. Эту опцию, кстати, можно добавить на панель быстрого доступа. Параметры Excel — Панель быстрого доступа — Все команды — Автофильтр. Да, она называется в списке команд просто как "Автофильтр", но это именно фильтрация по выделенной ячейке. Теперь у вас всегда под рукой кнопка, с помощью которой можно отфильтровать диапазон по значению активной ячейки, даже если фильтра в нем еще нет.

Меняем регистр в Word: Shift + F3 Лемур уверен: многие пользователи Ворда знают это сочетание клавиш, но вряд ли все. Так что стоит об этом напомнить! Итак, Shift + F3 меняет регистр слова (на котором курсор) или выделенного фрагмента. Верхний-Нижний-Каждое С Заглавной

Горячие клавиши по понедельникам🔥 Вставка только значений Ctrl + Shift + V очень удобно, чтобы: — заменить формулы на значения в Excel или Google Таблицах — вставить текст без форматирования из внешнего источника на слайд Google Презентаций В старых версиях Excel сочетание не работает. Что можно сделать: - Открыть диалоговое окно "Специальной вставки" Alt + Shift + V - нажать "з" или v (в зависимости от языка Excel) - нажать Enter.

Функция ЛИСТ / SHEET Возвращает она порядковый номер (индекс) листа. И этот номер может меняться. Он зависит от положения листа — они нумеруются от 1 до N, где N — количество листов в книге. Скрытые листы считаются. Функция без аргументов будет возвращать номер листа, на котором находится: =ЛИСТ() С аргументом (ссылкой) будет возвращать номер листа, на который ссылка: =ЛИСТ(Лист2!A1) Если лист переместить, то его номер меняется. Соответственно, можно придумать формулу с проверкой. Например, такую, которая будет сигнализировать об ошибке, если лист с оглавлением передвинуть вправо (как на видео): =ЕСЛИ(ЛИСТ()>1; "Ошибка!Переместите лист в начало книги";"Оглавление")

У нас есть два списка, каждый из которых в отдельной ячейке. Нам нужно получить общие для обоих списков значения. С новыми функциями 365 это можно сделать формулой. Сначала разделяем каждый из списков на отдельные значения с помощью ТЕКСТРАЗД / TEXTSPLIT. Получаем два массива (для наглядности на скриншоте этот и промежуточные шаги показаны отдельно в ячейках, а вся формула видна в строке формул). Затем ищем весь первый список (каждое значение из него) во втором. Для этого подходит ПОИСКПОЗ / MATCH или ПОИСКПОЗX / XMATCH, отличаются они только тем, что у первой (старой) функции нужно задать третий аргумент = 0 для точного поиска. Она выдаст либо порядковые номера найденных значений, либо ошибки. Мы превратим с помощью ЕЧИСЛО / ISNUMBER числа в ИСТИНЫ, а ошибки в ЛОЖЬ. И по полученному массиву отфильтруем с помощью ФИЛЬТР / FILTER — получим только те значения из первого списка, для которых ИСТИНА, то есть которые были найдены ПОИСКПОЗОМ во втором списке. Останется склеить это с помощью ОБЪЕДИНИТЬ / TEXTJOIN. Функция LET позволяет задать переменные для списков и потом ссылаться на них, а не повторять вычисление с функцией ТЕКСТРАЗД. Также мы объявляем переменную для списка элементов, который получается в результате работы функции ФИЛЬТР.

Точку данных в диаграмме (например, в линейчатой) можно заменить изображением Для этого скопируйте изображение (Ctrl + C), выделите диаграмму, выделите нужный элемент (просто щелкните еще раз после выделения диаграммы на нужный элемент — вы поймете, что он выделен, когда круглые маркеры по углам останутся только у этого столбика). И Ctrl + V — вставляем изображение. После этого можно зайти в панель форматирования (Ctrl + 1), чтобы уменьшить боковой зазор между столбиками. Тогда они станут шире/выше. И наконец, чтобы у вас не был один очень длинный кот или утка или что там у вас было на вставленной картинке, выберите в параметрах (Параметры ряда — Границы и заливка) вариант "размножить".

Горячие клавиши по понедельникам🔥 Сегодня перемещение по листам и вкладкам в диалоговых окнах Excel Все просто: Ctrl + PgUp (Page Up) — вперед Ctrl + PgDn (Page Down) — назад При этом в случае с окнами движение будет идти по кругу, то есть если вы на последней вкладке — нажав Ctrl + PgUp, окажетесь на первой. А в комменты выложим PDF с горячими клавишами за все недели!

Выводим все остальные книги автора в одной ячейке списком с буллетами Вот такая была задачка от коллег. Что делаем: 1 добавляем маркер ко всем названиям книг из исходного диапазона "• " & 'Книги'!A2:A 2 Находим имя автора конкретной книги, выбранной в выпадающем списке ПРОСМОТРX(A2; 'Книги'!A:A; 'Книги'!C:C) 3 Находим все его книги функцией FILTER. Условие на столбец с именем автора: ('Книги'!C2:C = ПРОСМОТРX(A2; 'Книги'!A:A; 'Книги'!C:C) 4 Исключаем ту книгу, которая уже выбрана — у нас ведь "другие" книги автора: ('Книги'!A2:A <> A2) Перемножаем эти два условия и отправляем в FILTER (это в Excel, а в Google Таблицах условия можно перечислять как отдельные аргументы — подробнее про FILTER и условия в большой статье по ссылке). На выходе будет список книг, но это будет массив. FILTER( "• " & 'Книги'!A2:A; ('Книги'!C2:C = ПРОСМОТРX(A2; 'Книги'!A:A; 'Книги'!C:C)) * ('Книги'!A2:A <> A2)) А нам надо все в одной ячейке. Так что объединяем функцией JOIN — да еще добавляем перенос строки через функцию СИМВОЛ / CHAR — его код 10: =JOIN( СИМВОЛ(10); FILTER(... )) Google Таблица с примером В новом Excel будет аналогично, только вместо JOIN — функция ОБЪЕДИНИТЬ / TEXTJOIN.