Что вы узнаете
- Как оформлять даты, валюты и другие форматы автоматически.
- Как настраивать ширину и высоту ячеек.
- Как закреплять строки и столбцы.
- Как настраивать выпадающие списки с любым количеством категорий.
- Как переносить таблицы на другие листы, не ломая формул.
- Как скрывать поля с ненужной информацией.
Этот урок — особенный. В нем мы не будем рассматривать одну тему или схожие функции. Здесь мы собрали самые разные советы по оформлению и оптимизации любых таблиц. Вы пройдете его за 15—20 минут.
Сегодня наша задача — привести в порядок вот эту таблицу. Она есть в вашей рабочей тетради:
Сейчас в ней ячейки разного размера, надписи не влезли, у чисел нет указаний валюты, есть ненужные знаки после запятой, ее неудобно прокручивать до конца, а из-за ячеек с актуальными на сегодня суммами все перемешивается и выглядит неаккуратно. Давайте наведем здесь порядок.
СОВЕТ 1
Используйте форматы данных
Вкратце мы уже говорили о форматах данных: каждое число в таблице можно красиво оформить, например проставить единицу измерения.
По умолчанию таблица не указывает единицу измерения — если мы ввели число 50 000, таблица покажет его просто как 50000. Но если мы хотим указать, что это, к примеру, 50 000 Р, не нужно вручную приписывать к числу слово «рублей» или символ Р: таблица этого не поймет. Достаточно выделить ячейки, в которых вы хотите поменять формат, и выбрать нужный формат из предложенных в меню:
Если нам не нужны копейки, можно скрыть их — уменьшить через меню количество знаков, которые отображаются после запятой:
Копейки в этом случае не стираются, таблица просто прячет их под капот, а для нас округляет до целого. Например, число 82,5845 будет округляться до 83. Но если взять это число и умножить на два, получится 165,169 — и таблица округлит его до 165, а не до 166.
Самостоятельно проставьте формат для долларов и евро — это третья и четвертая строки нашей таблицы.
Форматирование можно копировать с одной ячейки на другую. Есть два варианта: через меню или горячими клавишами.
Через меню: выделяем ячейку, формат которой мы хотим скопировать на остальные, жмем кнопку «Копировать форматирование». Затем выделяем все ячейки, к которым мы хотим применить нужный формат:
Это же можно сделать горячими клавишами. Выделяем ячейку с нужным форматом, копируем, как обычно, через Ctrl + C, выделяем диапазон, который хотим оформить, и вставляем комбинацией Ctrl + Alt + V (или ⌘ + Alt + V на Маке) — так вставится только стиль скопированной ячейки, без содержимого. Попробуйте сами.
Форматов очень много, и их можно настраивать. В меню выведены основные форматы — валюты, дата и время, но есть и дополнительные форматы. К примеру, вы хотите, чтобы дата в таблице была с месяцем прописью: не 01.08.2020, а 1 августа 2020. Так тоже можно. Выделяем ячейку, которую хотим отформатировать, идем в пункт форматирования «Пользовательские дата и время». В открывшемся меню будут варианты оформления — и среди них есть тот, где месяц прописан словом:
Теперь в таблице месяц будет указываться словом, но если нажать на ячейку, то в поле ввода будет обычный формат, который понимает таблица:
Скопируйте такой формат на все остальные проставленные даты.
СОВЕТ 2
Подстраивайте текст под ячейки и ячейки под текст
Теперь в нашей таблице аккуратно проставлены символы валют и убраны лишние знаки после запятой, но текст не помещается в ячейки целиком и обрывается:
Есть несколько способов этого избежать.
Можно сделать ячейку шире. Чтобы сделать это вручную, нужно зажать мышкой правый край столбца, который хотите расширить, и потянуть. Так можно настроить нужную ширину для каждого столбца:
Еще можно автоматически подстроить ячейку под длину текста — для этого нужно дважды кликнуть по тому же правому краю столбца:
Таким же образом можно подстраивать ширину ячейки не каждого столбца по отдельности, а всех вместе — и у каждого в результате будет индивидуальная ширина, при которой отображается все содержимое. Нужно выделить столбцы, которые мы хотим расширить, и дважды кликнуть по правому краю любого из них:
Можно отображать текст в несколько строк. Иногда текст в ячейках слишком длинный — и если подогнать под него ширину столбца, то таблицу слишком сильно растянет:
В этом случае увеличить нужно не ширину ячейки, а ее высоту — чтобы текст расположился в несколько строк. На панели инструментов для этого есть кнопка «Перенос текста» — если нажать на нее, можно увидеть, по каким правилам будет переноситься текст в выделенной ячейке. Нам нужен вариант с переносом текста на следующую строку — изогнутая стрелочка:
Когда правило переноса включено, текст подстраивается под ширину столбца — если не помещается целиком, часть переносится на следующую строку:
СОВЕТ 3
Закрепляйте столбцы, чтобы не теряться
Когда таблица становится очень большой, все ее ячейки могут не помещаться на один экран — ее придется прокручивать. И тут возникает такая ситуация:
Чтобы посмотреть более поздние данные, таблицу приходится прокручивать вправо. Но при этом скрывается наш столбец A, в котором у нас расписано, на каком именно счете лежат деньги. Получается, чтобы понять, на каком счете у нас к апрелю накопилось 55 тысяч, нам нужно прокрутить таблицу обратно в начало или запомнить, на какой строке какой счет расположен.
Но есть и другой выход — можно закрепить первый столбец таблицы, чтобы он всегда показывался на экране:
Теперь можно сколько угодно прокручивать таблицу вправо: мы всегда будем видеть столбец A и понимать, на каком счете лежат деньги из каждой строки.
Эта настройка находится в меню «Вид» → «Закрепить». Чтобы закрепить первый столбец, выбираем в меню «1 столбец». Можно закрепить и первую строку — если выбрать пункт «1 строка». Тогда можно будет сколько угодно прокручивать таблицу вниз, а первую строку все равно будет видно:
Таким же образом можно закреплять не только первую строку или столбец, но и вообще любые. Например, мы хотим рядом с названием каждого счета отдельным столбцом прописать его валюту. Первым делом для этого нужно создать дополнительный столбец — кликаем на столбец B правой кнопкой и выбираем «Вставить столбец слева»:
Теперь нам нужно закрепить первые два столбца, чтобы их постоянно было видно. Нужно выделить ячейку во втором столбце (B) и выбрать пункт «Закрепить до столбца B»:
В «Гугл-таблицах» есть более изящный способ закрепить любое количество строк и столбцов — но он не сработает в «Экселе». В верхнем левом углу таблицы есть рамочка с толстыми темно-серыми краями. Достаточно навести курсор на эту рамку — он превратится в руку, — зажать и протянуть рамку на нужное количество столбцов:
СОВЕТ 4
Настраивайте выпадающие категории
Теперь наша таблица выглядит намного аккуратнее, но мы можем ее оптимизировать: добавить выпадающие списки. Это бывает полезно, когда у вас есть ограниченный набор данных, которые вы хотите вводить в ячейку. Например, названия валют в нашем столбце B.
Сделаем так, чтобы валюты нужно было не вводить вручную, а выбирать из вот такого списка:
Есть два варианта, как это сделать, — попробуйте в рабочей тетради.
Прописать вручную. Подходит, когда у вас мало категорий. Например, в нашем примере три валюты: рубли, доллары и евро.
Сначала нужно выделить диапазон, в который будем вносить категории. Лучше выбрать его с запасом на следующие строки вниз, чтобы проверка работала и в следующих строках, если вы будете добавлять счета. Затем жмем «Данные» → «Настроить проверку данных»:
В открывшемся меню в строке «Правила» выбираем «Значение из списка» — и в строке справа прописываем все валюты, которые нам нужны в списке, через запятую:
Жмем «Сохранить» — список готов!
Можно не прописывать вручную, а составить справочник — то есть выписать данные, которые нужны в выпадающем списке, в отдельный диапазон в таблице и обращаться к нему.
Предположим, вам нужно, чтобы в выпадающем списке было не три валюты, а 10 — ну а вдруг! И в дальнейшем вы не исключаете, что расширите этот список до, например, 15. Можно создать отдельный лист, добавлять на него нужные вам валюты — и они будут автоматически добавляться в выпадающий список:
Настроим такой же список — в вашей рабочей тетради уже есть дополнительный лист «Валюты», который нам пригодится.
Начало такое же: выделяем все ячейки, в которых должен быть наш выпадающий список, и идем в меню «Данные» → «Настроить проверку данных».
Но сейчас в разделе «Правила» мы выберем не «Значение из списка», а «Значение из диапазона». В этом случае таблице нужно указать диапазон, откуда брать данные.
Нажимаем на сеточку таблицы около поля «Укажите диапазон»:
Теперь нужно выделить диапазон. Идем на лист «Валюты», где выписаны нужные нам валюты, и выделяем весь столбец А — можно просто нажать на сам заголовок столбца:
Жмем «Сохранить». У каждой ячейки с категорией появилась стрелочка — если нажать на нее, можно выбрать валюту из выпадающего списка.
Теперь, если вы просто добавите в столбец А листа «Валюты» новые данные, они автоматически подтянутся в выпадающий список. У вас заготовлено еще пять валют в столбце С — перенесите их в столбец А и проверьте, появились ли они в выпадающем списке. Соответственно, если удалить ненужные валюты с листа «Валюты», они пропадут из выпадающего списка.
СОВЕТ 5
Не городите все на один лист
Весь наш урок ниже списка трат болталась сводная табличка — с нынешним курсом валют и актуальной суммой наших активов:
Сейчас проблема в том, что сводная таблица прилипла к определенным столбцам. Из-за этого столбцы выглядят неаккуратно, а с течением времени, когда вы будете добавлять новые данные по деньгам, таблица будет смещаться вправо, а сводная таблица останется где-то в начале листа.
Да и логически тоже есть проблема: активы мы вносим вручную, а сводная таблица считается сама. Гораздо удобнее работать с таблицей, когда данные, которые нужно вводить вручную, собраны на одном листе — а уже на других все по этим данным считается автоматически.
Как этого добиться? Сначала разберемся, как устроена сводная таблица:
Актуальные курсы доллара и евро подтягиваются по формулам, которые вам уже известны из предыдущих уроков. Названия счетов в столбце D прописаны вручную. Балансы в столбце E подтягиваются из столбца R, где содержатся данные о состоянии активов на текущий месяц, — в ячейке E13 мы просто прописали =R2, чтобы ссылаться на данные из ячейки R2. И аналогично для следующих трех строк.
В столбце F мы переводим наши накопления в рубли. Если актив сам по себе рублевый, мы просто ссылаемся на соседнюю ячейку. Например, в ячейке F13 нам нужно просто вывести значение из ячейки E13 — так и напишем: =E13.
А если актив долларовый, нам нужно умножить его баланс на текущий курс доллара. Например, в ячейке F14 запишем формулу =E14*E8.
Затем в ячейке F19 суммируются все наши балансы, переведенные в рубли, — это все наши накопления в рублевом эквиваленте.
Вот эту таблицу мы и перенесем на отдельный лист, чтобы она не мешалась.
Чтобы разнести данные на несколько листов, есть два подхода — оба будут работать. Иногда удобнее пользоваться одним, иногда — другим.
Первый подход. Сначала записываем все на одном листе, а потом переносим на новый. Заготовка для этого варианта уже есть на нашем листе — ее и перенесем.
Создайте новый лист «Дашборд» — на него мы будем переносить сводную таблицу.
Переносить просто: нужно выделить все наши ячейки, которые хотим перенести, нажать «Вырезать», перейти на нужный лист и нажать «Вставить»:
При этом все связи между ячейками сохранятся: если мы брали баланс рублевого вклада из ячейки R2, он и в дальнейшем будет браться оттуда — но так как эта ячейка находится на другой странице, к ее адресу добавится имя листа. Будет не просто =R2, а ='Активы'!R2:
А если какие-то связи были прямо внутри перемещенной таблицы, адреса ячеек автоматически поменяются на новые. Помните, на первом листе мы переводили доллары в рубли с помощью формулы =E14*E8? В ячейке E14 у нас был указан баланс долларового счета, а в ячейке E8 — курс доллара. После переноса баланс долларового счета у нас оказался в ячейке B7, а курс доллара — в ячейке B1. Эти адреса в формуле поменялись автоматически:
Второй подход — изначально прописывать формулы на новых листах. Тут особенность как раз в адресах ячеек: чтобы таблица поняла, что ячейка находится на другом листе, нужно кроме адреса из столбца и строки дописать название листа — и из-за этого проще не прописывать название вручную, а указывать на ячейку. Для этого нужно поставить знак =, а потом курсором перейти на другой лист и выделить нужную ячейку:
В следующем уроке мы расскажем, как автоматизировать сводную таблицу, чтобы при добавлении данных за следующий месяц они подтягивались автоматически.
СОВЕТ 6
Скрывайте ненужные поля, чтобы не захламлять лист
Это самый простой и очевидный совет: в нашей таблице данные начинаются с августа 2020 года, а актуальные для нас суммы находятся далеко в правой части листа. Если нам не нужны перед глазами данные за прошлые месяцы, их можно смело скрыть — они не удалятся, просто временно исчезнут. Потом их так же можно будет показать.
Нужно выделить столбцы, которые вы не хотите видеть, и нажать «Скрыть столбцы»:
После скрытия на месте столбцов появится небольшая стрелочка — если нажать на нее, столбцы снова появятся:
Теперь наша таблица выглядит аккуратно и понятно — и ею стало намного удобнее пользоваться. Если на каком-то этапе у вас что-то не получилось, можете посмотреть финальную версию таблицы.
Что вы узнали
- С помощью форматов данных можно придать ячейкам любой вид: отображать там рубли, доллары, иены, даты в любом сочетании дня, месяца и года.
- Если текст в ячейке слишком длинный, можно сделать ячейку шире или отображать текст в несколько строк.
- Если какие-то строки или столбцы таблицы всегда должны быть перед глазами, их можно закрепить с помощью меню «Вид» → «Закрепить».
- Выпадающие списки отлично помогут, если нужно вводить много похожих повторяющихся данных.
- Формулы с одного листа можно безболезненно вырезать и вставлять на другой, связь между данными не потеряется.
- Если на листе есть столбцы или строки, которые вам не нужны, их можно скрыть без потери данных.