Как сделать формулу в экселе для различных нужд на все случаи жизни?

Доброго всем времени суток, мои дорогие друзья и читатели моего блога. Занимаясь различными интернет-проектами, мне приходится вести свои таблицы по доходам, расходам, планам и т.д. И очень сильно в этом мне помогает табличный редактор excel, который входит в пакет Microsoft Office. Поэтому сегодня я вам хочу рассказать, как сделать формулу в экселе, чтобы облегчить себе выполнение многих задач.

Основы

В экселе существует целое множество формул, благодаря которым многие операции просто напросто упрощаются. С помощью формул вы сможете посчитать сумму, разность, найти среднее значение, и многое другое. Для этого существует специальная отдельная вкладка, которая так и называется «Формулы». Нажмите туда.

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

Также активировать написание формул можно с помощью значка fx. Нажав на него, у вас откроется новое окно, где вы можете выбрать любую из категорий, либо же выбрать любую из 10 недавно использовавшихся категорий.

Математические функции

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

Простейшая формула

Самым простым способом создать выражение по формуле — это поставить знак равенства в любую ячейку. После этого вам нужно будет выбрать первое слагаемое, либо целый диапазон. После этого ставите плюс и выделяете другую ячейку. Кроме сложения вы можете использовать и другие знаки, такие как умножить, вычесть (минус), разделить. Все это выражение будет записано в одной клетке, и после нажатия клавиши Enter вы увидите нужный результат.

Сложение

Допустим у вас есть некоторое количество чисел, которые вы хотите сложить. Все делается очень просто. Нажмите на ту ячейку, где вы хотите видеть сумму ваших чисел, после чего нажмите на кнопку «Математические» и из списка выберите СУММ.

У вас откроется отдельное окно, куда вы должны вставлять числа для данного примера. Можно, конечно, писать простые цифры, но сам эксель предполагает, что будете брать данные из ячеек, ведь на то и расчет, чтобы можно было потом менять данные в ячейках. Поэтому выделяете ту ячейку (или ячейки), которые вы хотите брать в качестве слагаемых.

Например, выделим три моих столбца. Обратите внимание, я выделил всё сразу и в данном окне сразу видна сумма. Мне останется просто нажать Enter, чтобы сумма прописалась в нужную ячейку. При этом нам больше ничего не нужно.

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

Сложение с условием

А что, если вы хотите складывать все ячейки, а только те, которые, например меньше 20? Не будете же вы складывать каждое число в ячейке, если таких чисел 500? Вот здесь-то нам и поможет Сложение с условием.

Допустим, у нас есть много чисел, но нам надо сложить лишь те, которые меньше 20. Тогда мы выделяем как обычно пустую ячейку, и выбираем функцию СУММЕСЛИ. Встаем в первую строчку и выделяем весь диапазон ячеек. Затем встаем во вторую строчку и вставим нужный критерий, например «<20». Это и будет означать, что по формуле у нас выйдет сумма только тех ячеек, значение в которых меньше 20, а значит числа 42 и 43 не будут участвовать в этой сумме.

Точно также будут работать функции «ПРОИЗВЕД» и «ЧАСТНОЕ», только там уже, насколько вы поняли будет использоваться умножение и деление. Но функция ЧАСТНОЕ работает без остатка, поэтому, если с помощью нее вы попытаетесь разделить 5 на 3, то результат будет 1.

Оператора вычитания в экселе нет, но он в принципе и не нужен. Чтобы произвести вычитание, достаточно просто поставить обычный знак минус, например = B3-B2.

Давайте заодно посмотрим несколько других математических функций:

• КОРЕНЬ — Вычисляет квадратный корень из числа. Все, что вам нужно будет сделать — это выделить любую пустую ячейку, выбрать эту функцию, когда откроется новое окно, выбрать число, из которого вы будете извлекать корень.
• ЦЕЛОЕ — Округляет число, до ближайшего меньшего целого, т.е. если вы возьмете число 2,99, то функция округлит его до 2, а не до 3.
• ЧЕТН и НЕЧЕТ — Округляет любое число до ближайшего целого четного или нечетного числа в сторону увеличения
• Ну и множество других функций, которые вы сможете попробовать сами. Главное понять саму суть.

Как сделать формулу с процентами?

К сожалению в экселе нет оператора, который отвечает за проценты, но это вовсе не значит, что мы не сможем их вычислить. Достаточно лишь сделать специальную формулу с процентами.

Допустим у нас есть общее число (200) в одном столбце и числа, из которых оно состоит состоит (35, 45, 50, 70) в другом столбце. Значит нам нужно встать в ячейку, где у нас будет отображаться результат, а затем выбрать процентный формат. Для этого можете нажать правой кнопкой мыши на этой ячейке и выбираем пункт «Формат ячеек». И уже во вновь открывшемся окне во вкладке «Число» выбрать процентный формат.

Теперь ставим знак равенства, нажимаем на ячейку меньшего числа (35), затем ставим знак деления (/) и нажимаем на ячейку общего числа (200). Ничего на 100 умножать не надо, так как мы уже поставили процентный формат. Значит просто нажимаем Enter. Вуаля. У нас получилось 17,5%. Точно также можно сделать и с остальными ячейками.

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

Это происходит из-за того, что результат берется относительно строк и столбцов, из которых мсы брали значения. То есть, если у нас был результат в ячейке C3 от деления A3 на B3, то потянув результат вниз, мы получим результат уже по другой формуле, например C4=A4/B4, C5=A5/B5. Все из-за относительности.

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

Абсолютные и относительные формулы

Слышали вы про такие понятия, как абсолютные и относительные формулы. Если нет, то внимательно смотрите. Допустим у нас есть таблица с какими-то параметрами в несколько строк и два столбца. Давайте встаньте в пустую ячейку и напишите туда знак равенства. Затем сложите два первых значения из первой строки, так, чтобы у вас в формуле было прописано =A3+B3. У вас получится некий результат.

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

Давайте проделаем то же самое, только немного видоизменим B3, а именно проставим в формуле $B$3. Результат в ячейке останется абсолютно таким же. Но попробуйте теперь потянуть, как и в прошлом примере, за уголок. Вы заметили изменения? Все значения теперь другие. Всё потому, что мы сделали значение $B$3 абсолютным.

Это значит, что  как бы мы не спускались и тянули уголки, для второго слагаемого будет использоваться именно значение из неизменной ячейки B3. То есть она буд-то бы фиксируется и относительность остается, только у А3.  Такая функция очень пригодится для вычисления процентов, о которых я рассказывал выше.

Текстовые функции

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

Часто у вас бывает такое, что вы пишите какой-либо текст, а когда смотрите на монитор, то выясняется, что он был написан с зажатым CAPS LOCK? Так вот в Экселе есть такая формула, которая убирает прописные буквы, заменяя их строчными.

Для того, чтобы создать такую формулу, войдите в «Текстовые» и выберите функцию «СТРОЧН». Ну а дальше выделите ту ячейку, где у вас написан ваш текст заглавными буквами. Дальше жмем «ОК» и все готово! Текст в норме. То же самое можно делать и наоборот, но за это отвечает функция «ПРОПИСН».

Вот еще некоторые текстовые формулы, которые вам могут пригодится:

• РУБЛЬ — присваивает числу денежный формат, подставляя в конце фирменный знак российского рубля.
• СЖПРОБЕЛ — Удаляет лишние пробелы из текста, если вдруг случайно было поставлено два и более.
• ЮНИСИМВ — очень интересная функция, которая преобразует число в соответствующий ему символ Юникода.

Дата и время

Если честно, то я не знаю, где бы им найти толковое применение. Просто, если взять Функцию «День», то она будет отображать день относительно месяца. Например, если брать число 31, то и отобразится число 31, так как это максимальное число дней в месяце. Но если поставить число 32, то в итоге отобразится единица. Функция как бы переносит число на следующий месяц, типа не 32 января, а первое февраля.

Пример таблицы с применением формулы

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

Доходы:

• Adsense
• РСЯ
• Тизерная сеть
• Партнеки
• Итого за день
• Итого за месяц
• Всего по каждому типу рекламы

Расходы:

• Услуги копирайтера
• Покупка ссылок
• Домен+хостинг
• Разовые услуги фрилансеров

Общая прибыль

Средний ежесуточный доход

Что мы делаем дальше? Теперь мы начинаем вставлять нужные формулы в различные ячейки. выделяем ячейку следующую за «Итого за день» и нажимаем на значок Fx, где нам нужно будет выбрать СУММ, после чего выделяем всю строчку, начиная от Adsenese, и кончая Партнеркой. В итоге у нас будет автоматически высчитываться сумма за один день.

Но неужели теперь придется так проделывать еще 30 раз для каждой строки? Конечно нет. Мы задали форулу и теперь мы можем ее просто дублировать на остальные строки. Для этого выделите ячейку, к которой мы применили формулу, а затем тянете ее вниз за правый нижний уголок, пока не выделятся все 30 строк. В результате, теперь сумма за каждый день будет высчитываться отдельно относительно каждой строчке.

Кстати необязательно идти так далеко и пользоваться математическими функциями. Сейчас я вам покажу, как всё можно сделать быстрее.

Нам нужно посчитать, сколько мы зарабатываем за месяц по каждому виду рекламы. Для этого выделяем весь столбец Adsense с 1 по 31 число, а затем идем во вкладку «Формулы» и нажимаем на значок «Автосумма». Ниже мы тут же увидим сумму по данному виду. Теперь, чтобы также рассчитать сумму за месяц по остальным видам рекламы, нам нужно просто точно также, как и в предыдущем случае, зажать мышкой на кончике ячейки, и протянуть ее по остальным значениям. Таким образом формула автоматически подставится и для других видов доходов.

Теперь займемся расходами. Здесь я решил не расписывать на каждый день, а свалить все в одну кучу. То есть просто пишем значения в эти ячейки. При необходимости можно сложить, например в случае с доменом и хостингом, или с ссылками. Для этого просто встаньте в нужную ячейку и поставьте знак «=» (без кавычек). После этого пишите все те суммы, которые вы тратили по этой статье расходов.

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

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

Ну и ради интереса в отдельной ячейке будем высчитывать автоматически средний ежесуточный доход. Для этого мы с вами поставим курсор в любую ячейку и выберем оператор СРЗНАЧ, после чего выделим диапазон наших доходов с 1 по 31 число. Очень удобно.

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

Ух, ну и длинная статья у меня сегодня получилась. Ну я надеюсь, что оно того стоило. В любом случае не забывайте подписываться на обновления моего блога, а также делиться материалами статьи в социальных сетях. Удачи вам. Пока-пока!

С уважением, Дмитрий Костин.