Что вы узнаете
- Как оформлять даты и валюты так, как хочется.
- Как настраивать ширину и высоту ячеек.
- Как ссылаться с одного листа на другой.
- Как настраивать выпадающие списки.
- Как избавляться от лишних данных.
- Как не теряться в больших таблицах.
СОВЕТ 1
Используйте форматы данных
Вкратце мы уже говорили о форматах данных: каждое число в таблице можно красиво оформить, например проставить единицу измерения.
Возьмем расходы: по умолчанию таблица не указывает единицу измерения — если мы ввели число 600, таблица покажет его просто как 600. Но если мы хотим указать, что это, к примеру, 600 рублей, не нужно вручную приписывать к числу слово «рублей» или символ «Р»: таблица этого не поймет. Достаточно выбрать нужный формат из предложенных в меню:
Если нам не нужны копейки, можно скрыть их — уменьшить через меню количество знаков, которые отображаются после запятой:
Копейки в этом случае не стираются, таблица просто прячет их под капот, а для нас округляет до целого. Например, число 600,3 будет округляться до 600. Но если взять это число и умножить на два, получится 1200,6 — и таблица округлит его до 1201:
Форматирование можно копировать с одной ячейки на другую. Есть два варианта: через меню или горячими клавишами. Откройте рабочую тетрадь — разберемся вместе.
Через меню: выделяем ячейку, формат которой мы хотим скопировать на остальные, жмем кнопку «Копировать форматирование». Затем выделяем все ячейки, к которым мы хотим применить нужный формат:
Это же можно сделать горячими клавишами. Выделяем ячейку с нужным форматом, копируем, как обычно, через Ctrl + C, выделяем диапазон, который хотим оформить, и вставляем комбинацией Ctrl + Alt + V (или ⌘ + Alt + V на macOS) — так вставится только стиль скопированной ячейки, без содержимого:
Форматов очень много, и их можно настраивать. В меню выведены основные форматы — валюты, дата и время, но есть и дополнительные форматы. К примеру, вы хотите, чтобы дата в таблице была с месяцем прописью: не 02.01.2021, а 2 января 2021. Так тоже можно. Выделяем ячейку, которую хотим отформатировать, идем в пункт форматирования «Другие форматы» и выбираем «Другие форматы даты и времени»:
В открывшемся меню будут варианты оформления — и среди них есть тот, где месяц прописан словом:
Выбираем его — и готово. В таблице месяц будет указываться словом, но если нажать на ячейку, то в поле ввода формулы будет обычный формат, который понимает таблица:
Посмотрите пример настроенных форматов в решебнике.
СОВЕТ 2
Подстраивайте текст под ячейки и ячейки под текст
Часто бывает, что текст не помещается в ячейку целиком и попадает на соседнюю. А если в соседней ячейке тоже есть какие-то данные, первый текст оборвется:
Есть несколько способов этого избежать.
Можно сделать ячейку шире. Чтобы сделать это вручную, нужно зажать мышкой правый край столбца, который хотите расширить, и потянуть. Так можно настроить нужную ширину для каждого столбца:
Еще можно автоматически подстроить ячейку под размер текста — для этого нужно дважды кликнуть по тому же правому краю столбца:
Таким же образом можно подстраивать ширину ячейки не каждого столбца по отдельности, а всех вместе — и у каждого в результате будет индивидуальная ширина, при которой отображается все содержимое. Нужно выделить столбцы, которые мы хотим расширить, и дважды кликнуть по правому краю любого из них:
Можно отображать текст в несколько строк. Иногда текст в ячейках слишком длинный — и если подогнать под него ширину столбца, то таблицу слишком сильно растянет:
В этом случае увеличить нужно не ширину ячейки, а ее высоту — чтобы текст расположился в несколько строк. На панели инструментов для этого есть кнопка «Перенос текста» — если нажать на нее, можно увидеть, по каким правилам будет переноситься текст в выделенной ячейке. Нам нужен вариант с переносом текста на следующую строку — изогнутая стрелочка:
Когда правило переноса включено, текст подстраивается под ширину столбца — если не помещается целиком, часть переносится на следующую строку:
СОВЕТ 3
Не городите все на один лист
В уроке про фильтры мы делали вот такую сводную таблицу с тратами:
С точки зрения пользователя она может быть не очень удобной, особенно когда данных станет больше: список трат будет расти вниз, а отчеты по месяцам — вправо. Да и логически тоже есть проблема: траты мы вносим ручную — это лист для работы. А отчет считается автоматически — это лист для аналитики. В таблицах необязательно располагать все на одном листе: мы можем ссылаться с одного на другой.
Чтобы разнести данные на несколько листов, есть два подхода — оба будут работать.
Сначала записываем все на одном листе, а потом переносим. Переносить просто: нужны стандартные функции «Вырезать» → «Вставить».
В седьмом уроке мы уже собрали все данные по сводной таблице на одном листе — откройте рабочую тетрадь на листе «Вводим расходы», сейчас разнесем эти данные на два листа.
В первую очередь создадим лист, на котором будет располагаться сводная таблица. Назовем его «Аналитика»:
Теперь все, что нам нужно, — выделить данные, которые хотим перенести на новый лист, нажать «Вырезать», перейти на новый лист, выделить ячейку, которая будет верхней левой в нашей таблице, и нажать «Вставить»:
Обратите внимание, что формулы сами обновились: в них появилась связь с другим листом таблицы. Сравните эти две формулы:
=FILTER(C:C;B:B=F2;MONTH(A:A)=1)
=FILTER('Вводим расходы'!C:C;'Вводим расходы'!B:B=A2;MONTH('Вводим расходы'!A:A)=1)
Эти формулы выполняют одинаковые действия: выводят список расходов по категории «Транспорт» за январь. Разница в том, что первая формула располагается на том же листе, что и сами траты, а вторая — на другом, при этом расходы ищет на листе с тратами.
Можно сразу писать формулу на новом листе. Для этого придется немного поработать курсором. В качестве простого примера выведем на листе «Аналитика» сумму всех трат с листа «Вводим расходы»:
Суммировать будем привычной функцией SUM(). Для этого начнем вводить формулу в нужной нам ячейке и введем ее до первой скобки — после этого формула попросит указать диапазон, который нужно просуммировать. В этот момент с помощью курсора переходим на лист с тратами и выделяем нужный столбец:
СОВЕТ 4
Настраивайте выпадающие категории
Продолжим наши примеры с тратами и категориями. Если вносить траты в таблицу, то по умолчанию каждый раз категорию нужно прописывать заново — и тут всегда есть риск опечататься или просто забыть название. Например, один раз записать «Продукты», а второй — «Супермаркеты». В итоге эти категории будут считаться отдельно.
Таких проблем можно избежать, если заранее определить для себя список нужных категорий и настроить их ввод не вручную, а вот из такого выпадающего списка:
Разберемся, как сделать такой же на листе «Выпадающий список» рабочей тетради.
Шаг 1. Выпишем все нужные категории на отдельный лист — назовем его «Справочник». Такой лист уже есть в вашей рабочей тетради:
Если в дальнейшем вы захотите добавить какие-то категории, достаточно вернуться на этот лист и дописать недостающие категории в список ниже.
Шаг 2. Настроим проверку данных. Вернемся на лист, на котором мы вводим расходы. Мы вводим их в столбце B — выпадающий список нам будет нужен именно там. Выделим диапазон, в который будем вносить категории, и нажмем «Данные» → «Настроить проверку данных»:
Нажимаем на сеточку таблицы около поля «Укажите диапазон»:
Теперь нужно выделить диапазон. Идем на лист справочника, где выписаны категории, и выделяем список — можно с запасом, вдруг решите потом добавить еще категорий:
Жмем «Сохранить». Теперь у каждой ячейки с категорией появилась стрелочка — если нажать на нее, можно выбрать категорию из выпадающего списка:
Если что-то пошло не так, сверьтесь с решебником.
СОВЕТ 5
Скрывайте технические поля, чтобы не захламлять лист
Если вы решаете задачу в несколько действий, в процессе появятся так называемые технические столбцы: формулы, выдающие какое-то промежуточное значение, с которым вы в дальнейшем тоже будете что-то делать.
К примеру, вы решили, что хотите держать свои накопления в двух валютах — в рублях и в долларах поровну. Раз в месяц вы хотите сверяться: вводить свои накопления в рублях и долларах и смотреть, сколько долларов нужно докупить:
Чтобы настроить такую таблицу, нужно выполнить несколько действий:
- Перевести нынешнюю долларовую часть в рубли, чтобы понять, какую долю общих накоплений она составляет.
- Посчитать, какой должна быть каждая доля в рублевом эквиваленте. То есть сложить все наши накопления на данный момент и поделить на два.
- Посчитать разницу между нынешней долларовой долей в рублевом выражении и той, что должна быть, — на эту сумму нужно купить долларов, чтобы было поровну.
- Перевести получившуюся сумму в доллары — вот столько долларов нам нужно купить.
Давайте медленно разбираться. Откройте лист «Доллары» в своей тетради.
Первое действие: переводим долларовую часть в рубли. Для этого нам нужно умножить наши накопления в долларах на нынешний курс доллара:
Второе действие: считаем, какой должна быть каждая доля. Для этого мы суммируем наши рублевые накопления с накоплениями в долларах, переведенными в рубли, — это все наши деньги. А потом делим на два — чтобы понять, сколько должна составлять доля в одной валюте:
Третье действие: считаем, сколько рублей нужно направить на покупку долларов. Для этого вычитаем из доли, которая должна быть, нашу реальную долю:
Четвертое действие: переводим полученные рубли в доллары по текущему курсу — чтобы понять, сколько долларов нам нужно докупить. Просто делим сумму в рублях на курс доллара:
Таким образом, мы вводим данные в ячейки B2 и B3, получаем результат в ячейке F3. Столбцы C, D и E — технические, нам не особо нужны эти расчеты. Их можно просто скрыть целиком. Выделяем столбцы, которые не хотим видеть, жмем правой кнопкой по их шапке и выбираем «Скрыть столбцы»:
Если что-то пошло не так с формулами, сверьтесь с решебником.
СОВЕТ 6
Закрепляйте столбцы, чтобы не теряться
Когда таблица становится очень большой, все ее ячейки могут не помещаться на один экран — ее придется прокручивать. И тут возникает такая ситуация:
Чтобы посмотреть более поздние данные, таблицу приходится прокручивать вправо. Но при этом скрывается наш столбец A — в котором у нас расписано, на каком именно счете лежат деньги. Получается, чтобы понять, на каком счете у нас к апрелю накопилось 133 тысячи, нам нужно прокрутить таблицу обратно в начало или запомнить, на какой строке какой счет расположен:
Но есть и другой выход — можно закрепить первый столбец таблицы, чтобы он всегда показывался на экране:
Теперь можно сколько угодно прокручивать таблицу вправо: мы всегда будем видеть столбец A и понимать, на каком счете лежат деньги из каждой строки.
Эта настройка находится в меню «Вид» → «Закрепить». Чтобы закрепить первый столбец, выбираем в меню «1 столбец». Можно закрепить и первую строку — если выбрать пункт «1 строка». Тогда можно будет сколько угодно прокручивать таблицу вниз, а первую строку все равно будет видно:
Таким же образом можно закреплять не только первую строку или столбец, но и вообще любые. Например, нам нужно закрепить первые четыре столбца. Нужно выделить ячейку в четвертом столбце (D) и выбрать пункт «Закрепить до текущего столбца»:
В «Гугл-таблицах» есть более изящный способ закрепить любое количество строк и столбцов — но он не сработает в «Экселе». В верхнем левом углу таблицы есть рамочка с толстыми темно-серыми краями. Достаточно навести курсор на эту рамку — он превратится в руку, — зажать и протянуть рамку на нужное количество столбцов:
Что вы узнали
- С помощью форматов данных можно придать ячейкам любой вид: отображать там рубли, доллары, йены, даты в любом сочетании дня, месяца и года.
- Если текст в ячейке слишком длинный, можно сделать ячейку шире или перенести текст на несколько строк.
- Формулы с одного листа можно безболезненно вырезать и вставлять на другой, связь между данными не потеряется.
- Выпадающие списки отлично помогут, если нужно вводить много похожих повторяющихся данных.
- Если на листе есть то, что вам не нужно, — скройте это.
- Если какие-то строки или столбцы таблицы всегда должны быть перед глазами, их можно закрепить с помощью меню «Вид» → «Закрепить».