Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах) - Ренат Шагабутдинов

Конечно, все подобные ссылки будут открываться только при наличии у открывающего доступа к таблице.

Из Excel в Google Таблицы и обратно

Данные из Excel можно копировать в Google Таблицы — и наоборот: достаточно выделить диапазон, скопировать его (Ctrl + C) и вставить в другом редакторе (Ctrl + V). Если вы хотите вставить данные без форматирования в Google Таблицах (в Excel — только в Microsoft 365), нажмите Ctrl + Shift + V.

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

В таком случае придется вызвать окно замены (Ctrl + H), вставить в него скопированный из исходных данных пробел и удалить его (оставить поле «Заменить на» пустым).

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

Скачать Google Таблицы на локальный диск в виде книги Excel можно двумя способами.

1. Находясь в самой таблице, выберите «Скачать» (Download) в меню «Файл» (File).

2. На Google Диске щелкните правой кнопкой и в контекстном меню выберите «Скачать».

Книгу Excel можно загрузить как любой другой файл на Google Диск. После этого с ней можно полноценно работать, не будут доступны лишь некоторые функции (например, скрипты).

У книг Excel на Диске своя иконка.

А в интерфейсе таблиц отображается формат, показывающий, что мы работаем не с Google Таблицей.

В меню «Файл» есть опция для сохранения книги Excel в формате Google Таблиц.

Кроме того, там же есть опция «Импортировать» для загрузки книг Excel с локального диска сразу в формате Google Таблиц.

Несколько слов о макросах

В этой книге мы не рассматриваем тему макросов подробно: ей посвящены отдельные издания (я рекомендую книгу «Профессиональное программирование на VBA», подробнее о ней — чуть ниже в списке литературы). Скажем несколько слов, чтобы у вас сложилось представление о макросах в первом приближении, если вы еще с ними не сталкивались.

Макросы — это программы на встроенном в Excel языке программирования VBA. В широком смысле задача макросов — управление данными. С помощью макросов можно автоматизировать рутинные операции и избавиться от ручной работы, ускорив обработку данных в Excel и исключив ошибки ручного ввода и/или обработки данных.

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

Visual Basic был выпущен в 1991 году и стал стандартным языком для написания сценариев в приложениях Microsoft.

Поскольку макрос — это программа, а программа теоретически может выполнить вредоносные действия, с определенного момента Microsoft стал выделять отдельные форматы для документов Excel с макросами. Обычный формат последних версий Excel — XLSX — не предусматривает сохранение макросов. В нем можно их создать, но после этого придется сохранить в одном из специальных типов с макросами. Это XLSM или XLSB.

В версиях Excel 97–2003 файлы были с расширением xls и не имелось разделения на книги с макросами и без.

Создать макрос можно двумя способами: ввести код (или скопировать фрагменты готового кода из книг или интернета) или записать макрос с помощью средства записи (макрорекордера).

После нажатия на эту кнопку начнется запись.

Пока макрос не остановлен, все ваши действия будут фиксироваться.

Запись макроса — это своего рода магнитофон: все действия, которые вы производите, записываются на «пленку» в виде кода VBA.

Запись макроса имеет свои ограничения. Как правило, записанный макрос приходится дорабатывать, оптимизировать, не все действия можно записать в принципе (невозможно своими руками создать ссылку «на все листы в книге, сколько бы их ни было» — вы можете создать ссылки на одну, десять или сто книг, но это будет фиксированная история, в записи макросов нельзя использовать циклы или оперировать переменными).

Но этот инструмент очень удобен для обучения и знакомства с инструкциями: если вы не знаете, как называется тот или иной объект или свойство, запишите макрос, проделав действия с ним, и посмотрите на результат.

МАКРОСЫ VS POWER QUERY

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

Наглядно об этом говорит вот эта иллюстрация из книги «Приручи данные с помощью Power Query в Excel и Power BI» (Кен Пульс, Мигель Эскобар).

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

Давайте рассмотрим пример — решение простой задачи, которую нельзя быстро реализовать встроенными средствами.

ПРИМЕР ПРИМЕНЕНИЯ МАКРОСОВ: ОГЛАВЛЕНИЕ

В Excel (как и во многих других приложениях) можно вставить ссылку на место в документе с помощью сочетания Ctrl + K.

После чего выбрать слева «Связать с» — «Место в документе».

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

И получаем ссылку для перехода на соответствующее место в документе.

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