Самые популярные формулы для работы в программе Excel

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

Что такое Excel и зачем он нужен?

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

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

Что можно делать в Excel, как правильно использовать его функции и как это все работает? Предлагаем основные часто используемые формулы.

Для того, чтобы применять простые формулы по сложению, вычитанию, умножению и делению, следует использовать знак равенства (=), который должен стоять первым, а далее уже следует задать константы, т. е. числовые значения, а также операторы вычисления, плюс (+), минус (-), звездочка (*) и косая черта (/). В зависимости от того, какое действие вам надо выполнить: прибавить, вычесть, умножить, разделить - проставляете по этой формуле нужные знаки и числа, и программа все автоматически считает.

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

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

2. Затем ставим знак равенства (=), а после него - константы и операторы, но не более 8192 знаков, которые планируем использовать для вычисления.

3. Например, вводим самое простое, когда нужно число прибавить к числу =2+2.

4. Далее следует нажать ВВОД (Windows) или Return (Mac). И сразу же получим результат.

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

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

Для примера можно ввести другую простую формулу, которая будет включать в себя не только сочетание, но и умножение =4+2*2. По тому же принципу прописываем формулу, только меняем знак, если нужно отнять, разделить или узнать разницу и т.д.

Использование автосуммы

Для того, чтобы быстро использовать формулу “АВТОСУММЫ” следует выбрать ячейки (весь столбец), где указаны нужные для суммирования числа. Далее следует нажать кнопочку “АВТОСУММА”, которая находится на вкладке “Главная”. Затем нажимаем нажать ВВОД (Windows) или Return (Mac).

После нажатия “АВТОСУММЫ”, эксель автоматом проводит формулу с целью просуммировать числа - СУММ.

Кроме того, в ячейке есть возможность еще одного варианта получения автосуммы, путем введения ALT+= (Windows) или ALT+ КОМАНДА += (Mac). В этом случае Excel автоматически высчитает функцию СУММ.

Рассмотрим пример: если складывать числа за определенный период, например, при подсчете расходов за месяц, следует выбрать пустую ячейку, которая располагается под столбиком с нужными нам цифрами. Далее, чтобы вывести результат, нажимаем “АВТОСУММА”, после чего в ячейке должна появится формула, а числа проссумируются и покажется результат.

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

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

Более того, есть еще одна хорошая возможность использования функции “АВТОСУММЫ” одновременно для нескольких, а не одной, ячеек. Можно выделить желаемые нам ячейки, кликнуть на “АВТОСУММУ” и получить результат суммирования сразу двух столбцов, а не только одного или другого.

Как объединить или разбить ячейки в эксель?

Если вам надо объединить или разбить ячейки в эксель, то сделать это довольно просто. Для этого потребуется:

• Выбрать ячейки, которые хотите объединить (они должны находиться рядом);

• Далее необходимо выбрать на вкладке “Работа с таблицами” - “Макет” - “Объединение” - “Объединить ячейки”.

По такому же принципу происходит и разбивка ячеек. Для этого надо сделать такие шаги:

• Выделить нужную зону, которую хотим разделить на большее количество ячеек.

• Далее проходим по вкладкам “Работа с таблицами” - “Макет” - “Объединение” - “Разделить ячейки”.

• Выбираем нужное нам количество новых ячеек по горизонтали и/или вертикали (число столбцом и число строк).

И получаем желаемое количество новых ячеек вертикально или горизонтально соответственно.

Как в экселе вычислить среднее значение

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

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

• Выбираем вкладку “Главная” - “Редактирование” - “Автосумма” - “Среднее” (AVERAGE) и нажать ВВОД.

Формула СЦЕПИТЬ в эксель

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

=СЦЕПИТЬ(ПРОПИСН(ЛЕВСИМВ(A1));ПРАВСИМВ(A1;(ДЛСТР(A1)-1))), в которой будет А1 – нужная нам ячейка, где и планируется замена. Заметьте, что написание должно быть строго правильным, иначе ничего формула не даст должного результата.

Также обратите внимание, что эта формула выполняет свою функцию не “поверх” ячеек, в которых меняются данные, а именно в той ячейке, в которой и прописана формула.

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

• Выделяем все нужные нам ячейки, которые мы изменяли;

• Нажимаем одновременно клавиши Ctrl+C;

• После этого (не меняя положения выделенных ячеек), одновременно нажимаем Ctrl+V;

• Далее в меню, которое выпало “Параметры вставки” выбираем “Только значения”;

• Получаем только текстовые значения, которые не содержат формул.

Как создать формулу в Excel с выбором конкретных ячеек

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

Для этого надо совершить такие шаги:

• Выделить ячейку, в которую желаем поместить непосредственно формулу;

• Прописать знак равенства (=);

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

• Далее следует вписать математический оператор, который хотим использовать, например, умножение (*).

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

• В конце нажимаем Enter на клавиатуре, после чего формула будет автоматически рассчитана, и мы увидим результат.

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

Как посчитать процент в экселе?

Если в таблицах необходимо просчитать процент, например, в случае отчетного квартала, то в экселе сделать это также несложно. Предположим, что в вашей компании было продано количество продукции на общую сумму 125000 грн, и вам требуется выяснить, сколько процентов составляет 20000 грн от конечного значения.

Для этой цели вам надо будет поделить 20 000 грн на 125 000 грн. Для этого необходимо использовать такую формулу C2: =B2/A2 в которой С2 - результаты, которые нам надо получить, В2 - наши 20000 грн, А2 - 125000 грн. В результате мы получаем значение 0,16. Такой итог без процентов видим из-за того, что данная ячейка не настроена на показ процентных данных.

Для того, чтобы 0,16 эксель начал показывать как проценты (перед тем обязательно следует удалить “0”), следует перейти на вкладку “Главная”, а затем выбрать “%”. И после этого уже появится корректное отображение процентов в привычном нам виде.

Формулы массива в Excel

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

Делаем следующие шаги без использования формулы массива:

• Для начала следует рассчитать по каждой нужной строке развитие;

• Используем функцию (MAX), чтобы найти наибольшее значение в итоговом столбце.

Теперь используем более сложную формулу массива.

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

Производим такие действия:

• Рассчитываем по каждой нужной строке данные;

• Чтобы определить максимальное значение среди всех данных, следует добавить функцию МАКС (MAX) и заменяем C2 на C2:C6, а B2 на B2:B6 (или другие нужные нам ячейки, где находятся нужные нам данные).

• Чтобы завершить редактирование этой формулы следует нажать комбинацию клавиш Ctrl+Shift+Enter.

Следует обратить внимание, что диапазон данных (тот самый массив констант) добавляется в память программы эксель, но не в диапазоне ячеек. Такой массив констант можно, например, обозначить в определенном виде: {19;33;63;48;13} (подставляем нужные нам значения).

Функция МАКС (MAX) обращается к этому массиву как к аргументу и дает результат использовать этот массив как аргумент и возвращает результат.

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

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

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

Функция “ИНДЕКС” в экселе

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

Функция “ИНДЕКС” в экселе: как ее использовать? Есть несколько способов и вариантов этой функции. Рассмотрим некоторые из них.

Извлекаем данные по указанному номеру ячейки. Как правило, это наиболее распространенный способ применения данной функции. Она используется тогда, когда есть необходимость извлечь данные из определенного диапазона столбца, но только тогда, когда мы знаем конкретный номер ячейки. В этом случае пример синтаксиса прописывается таким образом:

=ИНДЕКС(диапазон_столбец; порядковый_номер_ячейки)

Другой вариант тоже довольно популярен и о нем знает большинство пользователей программы эксель. При таком применении обычно используют функцию ИНДЕКС плюс ПОИСКПОЗ(MATCH). Вторая функция способна выдавать номера искомых значений в заданном диапазоне. Выглядит синтаксис так:

=ИНДЕКС(диапазон_столбец; ПОИСКПОЗ (порядковый_номер_ячейки))

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

=ИНДЕКС(диапазон; номер_строки; номер_столбца)

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

Определение выборки в программе Excel по условию

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

Например, нам необходимо узнать в прайсе самый недорогой товар. В этом нам поможет это видео:

Как сделать, чтобы порядковый номер в эксель ставился автоматически?

Если вам автоматически нужно пронумеровать строки, тогда следует прописать необходимые числа, примеры, 1,2 или четные 2,4. Затем выделить эти две ячейки и за правый крайний уголок потянуть вниз - тогда ячейки автоматически пронумеруются по возрастанию; или же потянуть вверх - тогда нумерация будет идти в обратном порядке, но вверх уже.

Как создать или изменить ссылку на ячейку?

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

К тому же такие ссылки можно применять не только в одной, а и в нескольких формулах одновременно. Поэтому системе следует указать данные, что мы используем:

• Обозначаем информацию из одной или нескольких ячеек в рабочем листе, которые располагаются рядом;

• Информацию из ячеек, которая находится в разных частях рабочего листа;

• Информацию из других листов этого же файла.

• Выбираем ячейку, куда будем вводить формулу.

• В месте “Строка формул” вводим знак равенства (=)

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

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