Слышите? #REF! #REF! #REF! Это рычат и пугают формулами таблицы. Но мы узнали у Рената Шагабутдинова, преподавателя и консультанта по Excel и Google Таблицам с опытом 10+ лет, что с этим делать. Делимся 7 лайфхаками для работы в Google Spreadsheets: форматирование, горячие клавиши для вызова команд из меню, работа с ссылками, сортировка, вычисление фрагмента, пользовательские числовые форматы.
Скоро запускается новый практикум — по продвинутым формулам и задачам. Это четыре занятия полного погружения. Стартуем в августе. Промокод BLOG традиционно даст скидку 30%.
Используйте таблицы
В Google Spreadsheets в 2024 году появились… таблицы. Это мы про аналог того, что в Excel называется «Таблицами» (Tables, в русскоязычном сообществе и книгах их называют «умными таблицами»).
Чтобы превратить просто диапазон в таблицу, выберите соответствующую команду в меню «Формат»:
Формат — Преобразовать в таблицу
Format — Convert to table
(Alt + O) + E
Теперь можно:
— ссылаться на столбцы таблицы вот так: Имя_Таблицы[Имя_Столбца] (и тогда все будущие строки этого столбца будут учтены в вашей формуле, да и читабельность получше
Начните вводить название таблицы в формуле — появятся подсказки со всеми ее столбцами.
— Не переживать за форматирование: оно будет распространяться на новые строки таблицы, внешний вид данных будет унифицирован.
— Устанавливать тип данных у столбцов (например, дата или текст или выпадающий список) — такая проверка данных, которая встроена в таблицу и тоже будет автоматом распространяться на добавляемые строки.
— Группировать данные по одному из столбцов таблицы.
Горячие клавиши для вызова команд из меню
Сочетание клавиш Alt + буква отправит вас в одно из меню. Какая буква? Как правило, первая буква названия меню на английском. То есть для меню «Данные» (Data) это будет Alt + D.
Можно посмотреть сочетания для всех меню, нажав Ctrl + /.
Обратите внимание, что когда вы вызываете меню сочетанием клавиш (а не кликом мышки), у каждой команды в меню появляются буквы! Это значит, что вы можете и дальше после вызова меню использовать клавиатуру, вызывая любую команду с помощью клавиш (без мышки).
Ссылки на лист, диапазон, ячейки, комментарии и фильтры в Таблицах
Находясь на листе, скопируйте ссылку в адресной строке браузера целиком — при переходе по ссылке будет открываться именно этот лист.
У такой ссылки окончание вида edit#gid=https://docs.google.com/spreadsheets/ d/1gF3rnC62IR9t7zkXwjakcc77_Y6EyZLgkyrMc7b5K_o/edit#gid=1204635281
Также можно получить ссылку на любую ячейку или диапазон ячеек — достаточно выделить их и вызвать контекстное меню правой кнопкой мыши и выбрать «Показать другие действия с ячейкой» — «Получить ссылку на эту ячейку / этот диапазон» (View more cell actions — Get link to this cell/range):
У таких ссылок будет окончание вида &range=диапазон:
https://docs.google.com/spreadsheets/d/1gF3rnC62IR9t7zkXwjakcc77Y6EyZLgkyrMc7b5K _ o/edit#gid=1524961521&range=A28:G29
При работе с фильтром в режиме фильтрации (Filter view) можно
скопировать ссылку на него в адресной строке браузера — с окончанием &fvid=…:
https://docs.google.com/spreadsheets/d/1gF3rnC62IR9t7zkXwjakcc776EyZLgkyrMc7b5K _ o/edit#gid=305223850&fvid=1583181288
Также можно получить ссылку на комментарий, если щелкнуть на три точки в его правом верхнем углу:
Конечно, все подобные ссылки будут работать только при наличии у открывающего доступа к таблице.
Сортируем данные по нескольким столбцам
Вроде бы все просто: для быстрой сортировки по одному столбцу можно воспользоваться опциями в меню «Данные» (Data). Сортировка будет производиться по тому столбцу, который сейчас выделен (целиком или любая ячейка).
Но если нужно отсортировать данные по нескольким уровням, воспользуйтесь опцией «Расширенные настройки сортировки диапазонов» (Advanced range sorting options):
Выберите опцию «Данные со строкой заголовка» — тогда в окне сортировки можно будет выбирать уровни по заголовкам (Компания, Продукт и так далее), а не по номерам столбцов (Столбец A, Столбец B, …):
Вычисление фрагмента формулы
Если выделить мышкой фрагмент формулы — ссылку на ячейку, арифметическое выражение, функцию или несколько вложенных функций, — появится всплывающая подсказка, показывающая, какое значение возвращает именно этот фрагмент.
Это очень помогает и при написании, и при анализе сложных формул, состоящих из нескольких функций, особенно если они созданы кем-то другим (или вами в далеком прошлом).
Вставляем ссылку — как на страницы в сети, так и на фрагменты таблицы
Просто нажмите Ctrl + K — это сочетание работает во многих приложениях — и выберите «Листы и именованные диапазоны», чтобы вставить ссылку на фрагмент текущей таблицы. Если же вы хотите вставить ссылку на страницу в сети, просто вставьте ее в соответствующее поле в окне (сразу после нажатия Ctrl + K нажмите Ctrl + V, и ссылка из буфера обмена будет вставлена туда, куда нужно)
Добавляем таблицам огонька: пользовательские числовые форматы
С помощью пользовательских форматов можно делать свои таблицы нагляднее, оформлять должным образом количественные данные, не трогая сами значения, и добавлять условия без условного форматирования — например, красить отрицательные числа красным или же добавлять какой-то символ к числам больше тысячи. А еще показывать телефонные номера в нужном формате и многое другое. И это — обычное форматирование, а не условное (которое может существенно тормозить работу документов в Google Таблицах).
Чтобы открыть окно для ввода пользовательского формата, выберите «Другие форматы чисел» в списке форматов:
Вот пример формата для темпа прироста и других показателей, когда нам нужно показать рост как что-то хорошее (зеленое и со стрелкой вверх или смайлом или другим символом), снижение как что-то плохое (или наоборот).
[Color10]+0%* ▲;[Color53]-0%* ▼
Через точку с запятой перечисляются форматы: сначала для положительных чисел, потом для отрицательных (далее можно задать третий формат для нуля и четвертый для текстовых значений, если актуально).
Здесь Color10 — код темно-зеленого цвета, Color53 — красного.
+0% — это процентный формат со знаком «плюс».
* (звездочка с пробелом) — это заполнение ячейки символом, идущим после звездочки. То есть сначала отображается число в процентном формате с нужным знаком (+ — для положительных, — — для отрицательных), затем ячейка заполняется пробелами (визуально — ничем) до конца, и в правом краю символ (в нашем случае треугольник).
В итоге получается следующий формат:
Обложка поста — freepik.com
