Что вы узнаете

  1. Как оформлять даты, валюты и другие форматы автоматически.
  2. Как настраивать ширину и высоту ячеек.
  3. Как закреплять строки и столбцы.
  4. Как настраивать выпадающие списки с любым количеством категорий.
  5. Как переносить таблицы на другие листы, не ломая формул.
  6. Как скрывать поля с ненужной информацией.

Этот урок — особенный. В нем мы не будем рассматривать одну тему или схожие функции. Здесь мы собрали самые разные советы по оформлению и оптимизации любых таблиц. Вы пройдете его за 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 года, а актуальные для нас суммы находятся далеко в правой части листа. Если нам не нужны перед глазами данные за прошлые месяцы, их можно смело скрыть — они не удалятся, просто временно исчезнут. Потом их так же можно будет показать.

Нужно выделить столбцы, которые вы не хотите видеть, и нажать «Скрыть столбцы»:

После скрытия на месте столбцов появится небольшая стрелочка — если нажать на нее, столбцы снова появятся:

Теперь наша таблица выглядит аккуратно и понятно — и ею стало намного удобнее пользоваться. Если на каком-то этапе у вас что-то не получилось, можете посмотреть финальную версию таблицы.

Что вы узнали

  1. С помощью форматов данных можно придать ячейкам любой вид: отображать там рубли, доллары, иены, даты в любом сочетании дня, месяца и года.
  2. Если текст в ячейке слишком длинный, можно сделать ячейку шире или отображать текст в несколько строк.
  3. Если какие-то строки или столбцы таблицы всегда должны быть перед глазами, их можно закрепить с помощью меню «Вид» → «Закрепить».
  4. Выпадающие списки отлично помогут, если нужно вводить много похожих повторяющихся данных.
  5. Формулы с одного листа можно безболезненно вырезать и вставлять на другой, связь между данными не потеряется.
  6. Если на листе есть столбцы или строки, которые вам не нужны, их можно скрыть без потери данных.