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

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

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

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

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

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

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

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

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

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

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

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

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

Вот по какой логике мы ее составили: нам начисляют проценты на сумму, которую мы положили на вклад, — это B2.

Проценты нам начисляют каждый день, а потом выплачивают разом. Если за год начисляют 5%, то за один день — 5%/365.

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

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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Что вы узнали

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

Что дальше

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

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