Уроки Excel
5 постов
Немного про Excel #2. Какие формулы необходимо знать и уметь, ч1
И я вас снова приветствую. Если вы читали мой предыдущий пост, то знаете, что я уже много лет работаю с Экселем в рамках своей специальности и хочу немного продвинуть - насколько это возможно - знания об этой отличной программе. И сегодня мы поговорим о том, какие же формулы должны, что называется, от зубов отскакивать.
Этой мой личный набор, который, вероятно, растянется на несколько постов. И дело не столько в количестве конкретных формул, сколько в том, что я хотел бы посвятить чуть больше времени тому, каким образом лучше каждую из них применять. Я не буду погружаться в совсем уж дебри, но постараюсь накидать побольше интересного. Ну что, погнали - начнем с простого...
=СУММ()
Вы будете смеяться, но как-то ко мне пришел стажер лет 20-ти, и когда я попросил его сложить соседние ячейки в Экселе, он начал писать "=A10+A11+A12". Тру стори. Так что давайте разберемся с одной из самых простых формул - формулой суммирования.
Для чего применяется? В основном для суммирования смежных ячеек. Помимо этого, в скобках через точку с запятой можно прописать несколько диапазонов для суммирования.
Пример 1 - вам понадобилось посчитать выручку магазинов, принадлежащих одному человеку из зафиксированной (по каким-то причинам) табличной формы.
В примере 1 мы выделяем нужные диапазоны и "протягиваем" формулу вниз. По итогу у нас получатся требуемые суммы на каждый день. Если вам кажется, что ровно такого же эффекта можно было бы добиться, написав "=C5+D5+F5" - вы будете правы, однако, я рекомендую всегда представлять себе, что таблица в примере состоит из 10 000 строк и 10 000 столбцов. Правда, в этом случае пришлось бы пользоваться совсем другой формулой, и о ней мы еще когда нибудь поговорим.
Здесь и далее: "протягиваем" - означает копируем формулу по вертикали или горизонтали. Далее пишу без кавычек. Для протягивания можно использовать как простое копирование, так и буквальное "протягивание" за угол ячейки. Но я не рекомендую привыкать ко второму - оно может сыграть с вами злую шутку при наличии скрытых ячеек и/или фильтра.
Что интересного можно сделать с этой формулой?
Ну для начала, ее можно использовать, когда есть необходимость посчитать сумму накопительным итогом - для этого фиксируем первую ячейку диапазона суммирования и протягиваем формулу вниз.
Если зафиксировать первую ячейку диапазона по вертикали (значок $ стоит перед цифрой, перед буквой пусто), то при протягивании мы получим в каждой последующей ячейке сумму всех предыдущих включительно.
Также можно использовать формулу для того, чтобы прибавить некую составную константу к ряду значений. Вдруг вам лень нарисовать еще одну формулу суммы?
Довольно далекий от практики пример, но если вдруг быстро нужно что-то прикинуть, можно и так - чтобы не делать дополнительных действий. Фиксируем диапазон с "расходами" с обеих сторон (значки $ как перед цифрой, так и перед буквой) и протягиваем.
=СЧЁТ()
Да, в русском Экселе буква Ё используется, и об этом нужно помнить. И вот тут у нас еще одна довольно простая формула, у которой, тем не менее, есть немало применений.
Для чего применяется? Для подсчета количества числовых значений в ячейках.
Выделили диапазон, получили результат - одиннадцать ячеек содержат числовые значения. Поставили две буквы "Х" в диапазон для проверки, количество числовых значений уменьшилось на 2 и равно девяти. Работает.
Что интересного можно сделать с этой формулой?
Ну например с помощью нее можно попробовать найти нечисловые значения в массиве данных. Как? Ну например вот так - я "спрятал" одно текстовое значение среди числовых из предыдущего примера:
Если протянуть формулу счёта аналогично тому, как мы протягивали формулу суммы для расчета накопительного итога, то в месте "ошибки" (т.е., нечислового значения) она выдаст "повтор".
Под повтором понимается результат - семь - то есть на диапазоне С5:С11 было семь числовых значений и на диапазоне C5:C12 было столько же. Таким образом, ошибка в ячейке C12. Способов поиска ошибок в данных великое множество, и в моей профессии все они пригождаются время от времени. Этот конкретный может показаться вам излишним, но поверьте - если нужно найти проблему в массиве на 30-50 тысяч строк, то лучше иметь все инструменты под рукой.
Еще одно полезное применение данной формулы - вычисление среднего арифметического из массива данных. Вы конечно всегда можете воспользоваться функцией СРЗНАЧ() или СРЗНАЧА(), но иногда удобнее нарисовать формулу самостоятельно. Общий вид ее будет такой:
=СУММ(массив1)/СЧЁТ(массив1)
Если сделать это накопительным итогом, то у вас получится этакое "накопительное среднее" - я хз есть ли для этого научный термин, но штука полезная для аналитики. Ближаший аналог - скользящее среднее, но оно немного попроще. К слову, с помощью этой формулы можно и его реализовать.
***
На самом деле, сегодня мы с вами на этом закончим. Две формулы, причем довольно простые. Но текст получился довольно объемный. Настоятельно рекомендую попробовать все описанное в нем, если это применимо к вашей работе. Ну или к хобби) А вот уже дальше мы с вами поговорим про такие страшные функции, как СУММЕСЛИ() и СУММЕСЛИМН(), про жуткие ВПРы и ГПРы, ну а потом и про ПОИСК(), ЗАМЕНИТЬ() и много чего еще. Но самое сложное лежит в умении их комбинировать - сегодня мы рассмотрели одну простую комбинацию, дальше будет сложнее.
Показать полностью
5
