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

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

На примере нескольких задач мы посмотрим, как найти разные варианты для решения и выбрать удобный для себя. Вы пройдете этот урок за 15—20 минут.

Какие задачи и как можно решить с таблицами

Начнем с главного: с помощью таблицы можно решить любую задачу, связанную с расчетами и анализом данных, — вопрос только в том, насколько сложным будет путь. Но и тут есть хорошая новость: большинство задач решаются стандартными функциями, которые вы уже знаете. Прелесть таблиц в том, что у одной и той же задачи может быть сразу несколько решений. Какое-то будет более изящным, какое-то — менее, но главное — чтобы устраивал результат.

Простейший пример — поиск среднего арифметического значения. Например, вы весь месяц тратили деньги и хотите посчитать, сколько в среднем уходит за день:

Если вы внимательно проходили курс, то помните, что для этого есть специальная функция AVERAGE(), по-русски — СРЗНАЧ(). Она суммирует значения во всех ячейках и делит результат на их количество. Если вы тратили деньги каждый день января, задача решается одной формулой: =AVERAGE(B:B). Она выведет средний расход за день.

Но представим, что функция для среднего значения совершенно вылетела из головы. Задачу все равно можно решить довольно легко: просуммируем все наши траты функцией SUM() и поделим на количество ячеек — то есть дней в месяце, 31. Результат сойдется до копейки — потому что это математика:

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

Возьмем пример посложнее. Вам нужно посчитать, сколько денег за год принесет вклад: 1 000 000 Р под 5% с капитализацией. Но при этом о функции FV(), с помощью которой можно решить задачу в одно действие, вы забыли. Ту же задачу можно решить логически.

Что мы имеем: предположим, сегодня 1 марта 2021 года. Мы открыли вклад, и теперь первого числа каждого месяца на него будут начисляться проценты по ставке 5% годовых. В последующие месяцы проценты будут начисляться и на изначально внесенную сумму, и на проценты, выплаченные раньше. Составим вот такой каркас таблицы — он уже есть в вашей рабочей тетради:

Заполним первые строчки, а потом протянем формулы, чтобы заполнилась вся таблица.

Шаг 1. В ячейке B2 должна быть сумма, которую мы изначально внесли на вклад, то есть 1 000 000 Р.

В ячейке C2 нам нужно посчитать, сколько процентов начислят за март. Формула в C2 такая: =B2*5%/365*(A3-A2). Вот по какой логике мы ее составили:

  1. Нам начисляют проценты на сумму, которую мы положили на вклад, — это B2.
  2. Проценты нам начисляют каждый день, а потом выплачивают разом. Если за год начисляют 5%, то за один день — 5%/365.
  3. Мы знаем ставку за день, теперь нужно понять, сколько начислят за месяц. Для этого нужно посчитать, сколько дней в каждом конкретном месяце. Вычислим разницу дат: из A3, 1 апреля, вычтем A2, 1 марта, — получим 31 день. Итого ставка за март — 5% / 365 × 31. Умножаем сумму нашего вклада, B2, на формулу для расчета ставки за месяц — 5%/365*(A3-A2).

Получившуюся формулу внесем в ячейку C2:

Шаг 2. Теперь посчитаем, какая сумма будет на вкладе в начале следующего месяца — ее внесем в ячейку B3. Для этого просто сложим сумму в начале предыдущего месяца и начисленные за месяц проценты — =B2+C2:

Теперь формулу в ячейке C2 можно протянуть вниз до 01.02.2022: февраль 2022 года — это последний месяц, за который нам начислят проценты. А формулу в ячейке B3 протащим до 01.03.2022 — в этот день мы заберем деньги.

Итог будет совпадать с результатом применения функции FV() с точностью до рубля, есть небольшое расхождение в копейках:

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

Как разобраться в решении нужной задачи

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

Гораздо важнее умение правильно сформулировать запрос, чтобы можно было его загуглить. И тут — еще одна хорошая новость: 99% задач, которые мы хотим решить в таблицах, уже кто-то решил. В лучшем случае для задачи есть конкретная функция, которую нужно применить, в худшем — определенная связка функций, в которой нужно разобраться.

В общем виде запрос для поиска будет выглядеть как «Эксель *как сделать что-то*». Советуем гуглить именно с формулировкой «Эксель», а не «Гугл-таблицы», потому что решений для «Экселя» в интернете намного больше — и в большинстве случаев они сработают и для «Гугл-таблиц».

Опять простейший пример: нам жизненно необходимо найти формулу для расчета среднего значения. Так и загуглим:

Нас интересуют ссылки с описанием функции. Первая ссылка предлагает воспользоваться пунктами меню — а вот вторая описывает функцию СРЗНАЧ(), которая нам и нужна. Можно даже не искать английский вариант, а воспользоваться функцией на кириллице. Если в вашей гугл-таблице настроены англоязычные формулы, перевод произойдет автоматически:

Пример посложнее. Предположим, у вас есть таблица, в которую вы ежемесячно записываете свои накопления:

И вы хотите сделать лист со статистикой, который будет показывать, сколько денег у вас есть конкретно сейчас:

В таблице с накоплениями актуальные суммы у нас всегда справа: там мы добавляем новые месяцы и обновляем данные. Поэтому фактически наша задача звучит так: с листа с накоплениями всегда нужно брать крайнее правое значение, оно самое новое. Нам нужны значения по каждой строке отдельно: вклад, инвестсчет, карта, подушка безопасности. А потом эти данные мы просуммируем, чтобы получить общие накопления.

Как суммировать, мы знаем, а как выводить крайнее правое значение — нет. Загуглим:

Формулировка может быть и другой, например «Эксель как выводить последнее значение». Решение все равно найдется.

И сейчас в очередной раз подтвердится тезис о том, что у одной задачи может быть множество решений. Откройте лист «Статистика» вашей рабочей тетради — давайте решим эту задачу.

Решение по первой ссылке из Гугла. Тут нам предлагают использовать функцию LOOKUP(), по-русски — ПРОСМОТР().

Если упростить, смысл такой: мы задаем функции LOOKUP() значение, которое она должна найти, и диапазон, где нужно искать, — и функция постепенно перебирает ячейки в диапазоне и ищет совпадение. Если не находит — выводит значение из ячейки, которую проверила последней, то есть из самой правой. Именно то, что нужно, — осталось только сделать так, чтобы функция случайно не нашла совпадения и точно выдала самую правую ячейку. Для этого просто попросим функцию искать очень большое число, которого точно нет в нашей таблице, например 999 999 999 999.

Выводить самое правое значение будем для каждого актива в отдельности. Начнем с вклада:

Теперь полученную формулу мы можем протянуть для остальных активов:

А в конце просто просуммируем полученные значения в ячейке B1:

Решение по второй ссылке из Гугла. Тут предлагают другой подход: сначала определить порядковый номер последней заполненной ячейки, а потом по этому номеру вывести ее содержимое. Так тоже можно.

Шаг 1. Определяем номер последней заполненной ячейки в каждой строке. Для этого понадобится функция COUNTA(), по-русски — СЧЕТЗ(). Она считает количество непустых ячеек — соответственно, это количество будет совпадать с порядковым номером нужной нам ячейки. Составим формулу для той строки, где записаны данные по вкладу, а затем протянем вниз, на остальные строки:

Шаг 2. Выводим значение, которое находится в нужной ячейке. Для этого предлагают воспользоваться известной нам функцией INDEX(). Функции нужно задать диапазон, из которого выводим нужную ячейку, и ее номер. Номер мы уже знаем — он в столбце H. Диапазоны тоже известны. Составим формулу для вклада:

Осталось протянуть эту формулу на остальные активы и просуммировать:

Результаты совпали — оба способа хорошие. Посмотреть готовые решения можно в решебнике, там и диаграмма есть.

Как разбираться в синтаксисе незнакомых функций

Когда вы вводите функцию, таблица сразу выдает подсказку, как она работает и что делает. Порой эти подсказки не очень-то понятные — но хотя бы объясняют, в каком порядке вводить параметры и что они означают. Например, вот подсказка к функции FILTER():

По ней сразу можно сделать несколько выводов:

  1. Первым делом нужно ввести диапазон, который мы будем фильтровать, — то есть указать, какую часть нашей таблицы мы хотим вывести фильтром.
  2. Затем через точку с запятой нужно ввести первое условие. В примере это A2:A26 > 5, то есть число в диапазоне A2:A26 должно быть больше пяти.
  3. Затем через точку с запятой вводится следующее условие. В подсказке сверху оно в квадратных скобках — то есть необязательное. Можно остановиться на одном условии, если это решит ваши задачи.

Что делать, если задача не поддается

Иногда встречаются особо сложные задачи, которые не получается решить просто с помощью Гугла. В этом случае нужно поискать глубже. На крупнейшие русскоязычные базы знаний по таблицам мы ссылались, когда искали решение в гугле в предыдущих примерах: planetaexcel.ru и excel2.ru. У «Планеты Excel» есть еще и форум — поиском по нему можно найти решение практически любой задачи. Но если и это не помогло, можно зарегистрироваться и задать свой вопрос сообществу.

И заглядывайте в наше сообщество любителей экселек в Т⁠—⁠Ж — там можно похвастаться своими наработками или спросить совета у бывалых.

Запомним

  1. С помощью таблиц можно решить любую задачу по расчетам и анализу данных — для этого необязательно разбираться в функциях.
  2. Одну и ту же задачу в таблицах можно решить десятком способов. Они будут различаться изяществом и количеством действий, но главное — чтобы они работали.
  3. Практически любая задача уже кем-то решена, главное — правильно погуглить.
  4. Не ленитесь читать справку и пояснения к функциям, это поможет лучше понимать их возможности.
  5. Если решение не получается найти даже в интернете — не стесняйтесь просить помощи у сообщества.

Функции, которые вы освоили

  1. LOOKUP() помогает вывести в нужный вам столбец значение из последней ячейки.
  2. COUNTA() определяет порядковый номер ячейки. После этого с помощью INDEX() можно вывести и значение, содержащееся в ней.

Что дальше

Вы прошли последний урок нашего курса — и мы даем вам доступ к финальной таблице, в которой собраны шаблоны по всем урокам курса. Эти шаблоны пригодятся для решения разных бытовых задач — от ведения бюджета до занятий спортом. Надеемся, они будут вам полезны.

Впереди вас ждет тест по материалам десятого урока, а после него — экзамен по всему материалу курса. Всем, кто успешно сдаст экзамен, на электронную почту пришлем именной диплом. Удачи!