Чтобы применить любую из перечисленных функций, поставьте знак равенства в ячейке, в которой вы хотите видеть результат. Затем введите название формулы (например, МИН или МАКС), откройте круглые скобки и добавьте необходимые аргументы. Excel подскажет синтаксис, чтобы вы не допустили ошибку.
Аргументами называют данные, с которыми работает функция. Чтобы добавить их, нужно выделить соответствующие ячейки или ввести необходимые значения в скобках вручную.
Есть и альтернативный способ указать аргументы. Если после названия функции добавить пустые скобки и нажать на кнопку «Вставить функцию» (fx), появится окно ввода с дополнительными подсказками. Можете использовать его, если вам так удобнее.
СРЗНАЧ
- Синтаксис: =СРЗНАЧ(число1; [число2]; …).
«СРЗНАЧ» отображает среднее арифметическое всех чисел в выбранных ячейках. Другими словами, функция складывает указанные пользователем значения, делит получившуюся сумму на их количество и выдаёт результат. Аргументами могут быть отдельные ячейки и диапазоны. Для работы функции нужно добавить хотя бы один аргумент.
Как посчитать количество строк (с одним, двумя и более условием)
Довольно типичная задача: посчитать не сумму в ячейках, а количество строк, удовлетворяющих какомe-либо условию.
Ну, например, сколько раз имя «Саша» встречается в таблице ниже (см. скриншот). Очевидно, что 2 раза (но это потому, что таблица слишком маленькая и взята в качестве наглядного примера). А как это посчитать формулой?
Формула:
«=СЧЁТЕСЛИ(A2:A7;A2)» — где:
- A2:A7 — диапазон, в котором будут проверяться и считаться строки;
- A2 — задается условие (обратите внимание, что можно было написать условие вида «Саша», а можно просто указать ячейку).
Результат показан в правой части на скрине ниже.
Количество строк с одним условием
Теперь представьте более расширенную задачу: нужно посчитать строки, где встречается имя «Саша», и где в столбце «B» будет стоять цифра «6». Забегая вперед, скажу, что такая строка всего лишь одна (скрин с примером ниже).
Формула будет иметь вид:
=СЧЁТЕСЛИМН(A2:A7;A2;B2:B7;»6″) — (прим.: обратите внимание на кавычки — они должны быть как на скрине ниже, а не как у меня), где:
A2:A7;A2 — первый диапазон и условие для поиска (аналогично примеру выше);
B2:B7;»6″ — второй диапазон и условие для поиска (обратите внимание, что условие можно задавать по разному: либо указывать ячейку, либо просто написано в кавычках текст/число).
Счет строк с двумя и более условиями
*
ЕСЛИ
- Синтаксис: =ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь]).
Формула «ЕСЛИ» проверяет, выполняется ли заданное условие, и в зависимости от результата отображает одно из двух указанных пользователем значений. С её помощью удобно сравнивать данные.
В качестве первого аргумента функции можно использовать любое логическое выражение. Вторым вносят значение, которое таблица отобразит, если это выражение окажется истинным. И третий (необязательный) аргумент — значение, которое появляется при ложном результате. Если его не указать, отобразится слово «ложь».
Окно вставки функции
Некоторые юзеры боятся работать в Экселе только потому, что не понимают, как именно устроены функции и каким образом их нужно составлять, ведь для каждой есть свои аргументы и особые нюансы написания. Упрощает задачу наличие окна вставки функции, в котором все выполнено в понятном виде.
- Для его вызова нажмите по кнопке с изображением функции на панели ввода данных в ячейку.
- В нем используйте поиск функции, отобразите только конкретные категории или выберите подходящую из списка. При выделении функции левой кнопкой мыши на экране отображается текст о ее предназначении, что позволит не запутаться.
- После выбора наступает время заняться аргументами. Для каждой функции они свои, поскольку выполняются совершенно разные задачи. На следующем скриншоте вы видите аргументы суммы, которыми являются два числа для суммирования.
- После вставки функции в ячейку она отобразится в стандартном виде и все еще будет доступна для редактирования.
СУММЕСЛИ
- Синтаксис: =СУММЕСЛИ(диапазон; условие; [диапазон_суммирования]).
Усовершенствованная функция «СУММ», складывающая только те числа в выбранных ячейках, что соответствуют заданному критерию. С её помощью можно прибавлять цифры, которые, к примеру, больше или меньше определённого значения. Первым аргументом является диапазон ячеек, вторым — условие, при котором из них будут отбираться элементы для сложения.
Если вам нужно посчитать сумму чисел не в диапазоне, выбранном для проверки, а в соседнем столбце, выделите этот столбец в качестве третьего аргумента. В таком случае функция сложит цифры, расположенные рядом с каждой ячейкой, которая пройдёт проверку.
Используем вкладку с формулами
В Excel есть отдельная вкладка, где расположена вся библиотека формул. Вы можете использовать ее для быстрого поиска и вставки необходимой функции, а для редактирования откроется то же самое окно, о котором шла речь выше. Просто перейдите на вкладку с соответствующим названием и откройте одну из категорий для выбора функции.
Как видно, их названия тематические, что позволит не запутаться и сразу отобразить тот тип формул, который необходим. Из списка выберите подходящую и дважды кликните по ней левой кнопкой мыши, чтобы добавить в таблицу.
Приступите к стандартному редактированию через окно аргументов функции. Кстати, здесь тоже есть описания, способные помочь быстрее разобраться с принципом работы конкретного инструмента. К тому же ниже указываются доступные значения, которые можно использовать для работы с выбранной формулой.
КОРРЕЛ
- Синтаксис: =КОРРЕЛ(диапазон1; диапазон2).
«КОРРЕЛ» определяет коэффициент корреляции между двумя диапазонами ячеек. Иными словами, функция подсчитывает статистическую взаимосвязь между разными данными: курсами доллара и рубля, расходами и прибылью и так далее. Чем больше изменения в одном диапазоне совпадают с изменениями в другом, тем корреляция выше. Максимальное возможное значение — +1, минимальное — −1.
Математические операторы
Одно из главных преимуществ Excel — проведение расчетов с помощью математических формул, делающих таблицы более удобными и гибкими.
Программа способна выполнять не только простые задачи, но и делать весьма трудные расчеты. Для начала рассмотрим стандартные действия:
- плюс (+) — сложение;
- минус (-) — вычитание;
- косая полоска (/) — деление;
- звезда (*) — умножение;
- символ крыши «домика» (^) — степень.
Для составления любой формулы Excel вначале необходимо поставить знак равно (=).
Для удобства в программе предусмотрена опция, позволяющая делать математические действия в привязке к номерам ячеек.
Суть в том, что пользователь использует конкретные адреса, после чего Excel выполняет расчеты.
Применение метода дает плюсы в виде уменьшения числа помарок и облегчения внесения изменений.
С учетом сказанного можно подвести итог, как правильно составить формулу. Для этого поставьте в нужную ячейку знак равно, а после этого укажите необходимые действия, к примеру, В1+В2 или А1*А2.
Thank you!
We will contact you soon.
СЦЕП
- Синтаксис: =СЦЕП(текст1; [текст2]; …).
Эта функция объединяет текст из выбранных ячеек. Аргументами могут быть как отдельные клетки, так и диапазоны. Порядок текста в ячейке с результатом зависит от порядка аргументов. Если хотите, чтобы функция расставляла между текстовыми фрагментами пробелы, добавьте их в качестве аргументов, как на скриншоте выше.
Формулы в Excel для чайников
Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.
В Excel применяются стандартные математические операторы:
Оператор | Операция | Пример |
+ (плюс) | Сложение | =В4+7 |
— (минус) | Вычитание | =А9-100 |
* (звездочка) | Умножение | =А3*2 |
/ (наклонная черта) | Деление | =А7/А8 |
^ (циркумфлекс) | Степень | =6^2 |
= (знак равенства) | Равно | |
Меньше | ||
> | Больше | |
Меньше или равно | ||
>= | Больше или равно | |
Не равно |
Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.
Программу Excel можно использовать как калькулятор. То есть вводить в формулу числа и операторы математических вычислений и сразу получать результат.
Но чаще вводятся адреса ячеек. То есть пользователь вводит ссылку на ячейку, со значением которой будет оперировать формула.
При изменении значений в ячейках формула автоматически пересчитывает результат.
Ссылки можно комбинировать в рамках одной формулы с простыми числами.
Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.
В нашем примере:
- Поставили курсор в ячейку В3 и ввели =.
- Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
- Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.
Если в одной формуле применяется несколько операторов, то программа обработает их в следующей последовательности:
- %, ^;
- *, /;
- +, -.
Поменять последовательность можно посредством круглых скобок: Excel в первую очередь вычисляет значение выражения в скобках.
Отображение и печать формул
является ссылкой на
секунд и сообщить, _з0з_. на листе формулы. A1 и A2 или ALT+ как плюс ( ту же формулу расчетов следует записать остаются постоянными столбец точку левой кнопкой Чтобы ввести в электронные таблицы неВозвращаемся к листу, на выставляем настройки удаления или диапазона. с формулами в другую формулу, нажмите помогла ли онаВ разделеЧтобы отобразить формулы в
=A2+A3+= (Mac), и+ для расчета выплаты, формулу в ячейку и строка; мыши, держим ее формулу ссылку на нужны в принципе. котором расположена интересующая и жмем наВыделяем всю область вставки другую область значения
кнопку вам, с помощьюПоказывать в книге ячейках, нажмите сочетание
Отображение всех формул во всех ячейках
=A2-A3 Excel автоматически вставит), минус ( что находиться в Excel. В таблицеB$2 – при копировании и «тащим» вниз
ячейку, достаточно щелкнутьКонструкция формулы включает в нас таблица. Выделяем
кнопку
или её левую могут «потеряться». ЕщёШаг с заходом кнопок внизу страницы.установите флажок клавиш CTRL+` (маленькийРазность значений в ячейках функцию СУММ.- F2, но уже из предыдущего урока неизменна строка; по столбцу. по этой ячейке.
себя: константы, операторы,
фрагмент, где расположены
«OK»
верхнюю ячейку. Делаем одним поводом их, чтобы отобразить другую Для удобства также
Отображение формулы только для одной ячейки
формулы значок — это значок A1 и A2Пример: чтобы сложить числа), звездочка ( другим эффективным способом (которая отображена ниже$B2 – столбец неОтпускаем кнопку мыши –В нашем примере: ссылки, функции, имена
Формулы все равно не отображаются?
формулы, которые нужно. щелчок правой кнопкой спрятать может служить формулу в поле приводим ссылку на. тупого ударения). Когда
=A2-A3 за январь в*
- копирования. на картинке) необходимо изменяется.
формула скопируется в
Поставили курсор в ячейку
диапазонов, круглые скобки удалить. Во вкладке
После выполнения указанных действий мыши, тем самым ситуация, когда выВычисление
- оригинал (на английскомЭтот параметр применяется только формулы отобразятся, распечатайте=A2/A3 бюджете «Развлечения», выберите) и косая черта
Задание 1. Перейдите в посчитать суму, надлежащую
Чтобы сэкономить время при
выбранные ячейки с
В3 и ввели - содержащие аргументы и«Разработчик»
все ненужные элементы
вызывая контекстное меню.
не хотите, чтобы - . Нажмите кнопку языке) .
к просматриваемому листу.
лист обычным способом.
Частное от деления значений ячейку B7, которая (
другие формулы. Нажмем на кнопку
будут удалены, а
В открывшемся списке
другие лица видели,
Шаг с выходом
В некоторых случаях общееЕсли формулы в ячейках
- Чтобы вернуться к отображению в ячейках A1
находится прямо под
/
нажмите комбинацию клавиш - 12% премиальных к в ячейки таблицы,
есть в каждой
Щелкнули по ячейке В2
примере разберем практическое - «Макросы» формулы из исходной
выбираем пункт
как проводятся в
, чтобы вернуться к представление о как
по-прежнему не отображаются,
результатов в ячейках, и A2 столбцом с числами.
).
CTRL+D. Таким образом, ежемесячному окладу. Как применяются маркеры автозаполнения. ячейке будет своя – Excel «обозначил» применение формул для, размещенную на ленте таблицы исчезнут.
- «Специальная вставка» таблице расчеты. Давайте предыдущей ячейке и вложенные формула Получает попытайтесь снять защиту
снова нажмите CTRL+`.=A2/A3
Затем нажмите кнопку
В качестве примера рассмотрим
автоматически скопируется формула,
в Excel вводить
Если нужно закрепить формула со своими
См. также
ее (имя ячейки
начинающих пользователей. в группе
Можно сделать ещё проще
support.office.com>
Абсолютные и относительные ссылки в Эксель
В процессе создания электронных таблиц пользователь неизбежно сталкивается с понятием ссылок. Они позволяют обозначить адрес ячейки, в которой находятся те или иные данные. Ссылка записывается в виде А1, где буква означает номер столбца, а цифра – номер строки.
В процессе копирования выражений происходит смещение ячейки, на которую оно ссылается. При этом возможно два типа движения:
- при вертикальном копировании в ссылке изменяется номер строки;
- при горизонтальном перенесении изменяется номер столбца.
В этом случае говорят об использовании относительных ссылок. Такой вариант полезен при создании массивных таблиц с однотипными расчетами в смежных ячейках. Пример формулы подобного типа – вычисление суммы в товарной накладной, которое в каждой строке определяется как произведение цены товара на его количество.
Однако может возникнуть ситуация, когда при копировании выражений для расчетов требуется, чтобы она всегда ссылалась на одну и ту же ячейку. Например, при переоценке товаров в прайсе может быть использован неизменный коэффициент. В этом случае возникает понятие абсолютной ссылки.
Закрепить какую-либо ячейку можно, используя знак $ перед номером столбца и строки в выражении для расчета: $F$4. Если поступить таким образом, при копировании номер ячейки останется неизменным.
Относительные ссылки
Абсолютные ссылки
Третий метод: замена при помощи мышки
Этот метод позволяет осуществить процедуру замены формул на значения намного быстрее вышеприведенных методов. Здесь используется только компьютерная мышка. Подробная инструкция выглядит так:
- Производим выделение ячеек с формулами на рабочем листе табличного документа.
- Беремся за рамку выделенного фрагмента и, зажав ПКМ, осуществляем перетаскивание на два см в какую-либо сторону, а затем реализуем возврат на начальную позицию.
- После осуществления этой процедуры отобразилось небольшое специальное контекстное меню, в котором необходимо выбрать элемент, имеющий наименование «Копировать только значения».
Далеко идущие выводы
- Формула введенная вручную
2. Формула с использованием данных, которые находятся в ячейках
3. Формула с использованием встроенной функции и данных, которые находятся в ячейках
4. Формула с использованием встроенной функции и данных, которые находятся в двух диапазонах
Теперь вы можете:
- Перечислить и показать арифметические операторы для создания формулы
- Вводить вручную формулы
- Использовать встроенные функции
- Выбирать нужные функции из контекстного меню
На этом занятии вы познакомились с базой, на которой построены все возможности мощного вычислительного аппарата Excel.
Пятый метод: использование специальных макросов
Использование макросов – самый быстрый способ, позволяющий заменить формулы на значения. Так выглядит макрос, позволяющий заменить все формулы на значения в выбранном диапазоне:
Так выглядит макрос, позволяющий заменить все формулы на значения на выбранном рабочем листике табличного документа:
Так выглядит макрос, позволяющий заменить все формулы на значения на всех рабочих листиках табличного документа:
Подробная инструкция по использованию макросов выглядит так:
- При помощи комбинации клавиш «Alt+F11» открываем редактор VBA.
- Открываем подраздел «Insert», а затем жмем левой клавишей мышки по элементу «Module».
- Сюда мы вставляем один из вышеприведенных кодов.
- Запуск пользовательских макросов осуществляется через подраздел «Макросы», располагающийся в разделе «Разработчик». Альтернативный вариант – использование комбинации клавиш «Alt+F8» на клавиатуре.
- Макросы работают в любом табличном документе.
Стоит помнить, что манипуляции, реализованные при помощи макроса, нельзя отменить.
Виды формул
Excel понимает несколько сотен формул, которые проводят не только расчеты, но и другие операции. При правильном введении функции программа подсчитает возраст, дату и время, предоставит результат сравнения таблиц и т.д.
Простые
Здесь не придется долго разбираться, поскольку выполняются простые математические действия.
СУММ
Определяет сумму нескольких чисел. В скобках указывается каждая ячейка по отдельности или сразу весь диапазон.
=СУММ(значение1;значение2)
=СУММ(начало_диапазона:конец_диапазона)
ПРОИЗВЕД
Перемножает все числа в выделенном диапазоне.
= ПРОИЗВЕД(начало_диапазона:конец_диапазона)
ОКРУГЛ
Помогает произвести округление дробного числа в большую (ОКРУГЛВВЕРХ) или меньшую сторону (ОКРУГЛВНИЗ).
ВПР
Это – поиск необходимых данных в таблице или диапазоне по строкам. Рассмотрим функцию на примере поиска сотрудника из списка по коду.
Искомое значение – номер, который нужно найти, написать его в отдельной ячейке.
Таблица – диапазон, в котором будет осуществляться поиск.
Номер столбца – порядковый номер столбца, где будет осуществляться поиск.
Альтернативные функции – ИНДЕКС/ПОИСКПОЗ.
СЦЕПИТЬ/СЦЕП
Объединение содержимого нескольких ячеек.
=СЦЕПИТЬ(значение1;значение2) – цельный текст
=СЦЕПИТЬ(значение1;» «;значение2) – между словами пробел или знак препинания
КОРЕНЬ
Вычисление квадратного корня любого числа.
=КОРЕНЬ(ссылка_на_ячейку)
=КОРЕНЬ(число)
ПРОПИСН
Альтернатива Caps Lock для преобразования текста.
=ПРОПИСН(ссылка_на_ячейку_с_текстом)
=ПРОПИСН(«текст»)
СТРОЧН
Преобразует текст в нижний регистр.
=СТРОЧН(ссылка_на_ячейку_с_текстом)
= СТРОЧН(«текст»)
СЧЁТ
Подсчитывает количество ячеек с числами.
=СЧЁТ(диапазон_ячеек)
СЖПРОБЕЛЫ
Убирает лишние пробелы. Это будет полезно, когда данные переносятся в таблицу из другого источника.
=СЖПРОБЕЛЫ(адрес_ячейки)
Сложные
При масштабных расчетах часто возникают проблемы с написанием функций или ошибки уже в результате. В этом случае придется немного изучить функции.
Важно! Критерии обязательно нужно брать в кавычки.
ПСТР
Позволяет «достать» требуемое количество знаков из текста. Обычно используется при редактировании тайтлов в семантике.
=ПСТР(ссылка_на_ячейку_с_текстом;начальная_числовая_позиция;число_знаков_которое_вытащить)
ЕСЛИ
Анализирует выбранную ячейку и проверяет, отвечает ли значение заданным параметрам. Возможны два результата: если отвечает – истина, не отвечает – ложь.
=ЕСЛИ(какие_данные_проверяются;если_значение_отвечает_заданному_условию;если_значение_не_отвечает_заданному_условию)
СУММЕСЛИ
Суммирование чисел при определенном условии, то есть необходимо сложить не все значения, а только те, которые отвечают указанному критерию.
=СУММЕСЛИ(C2:C5;B2:B5;«90»)
Исходя из примера, программа посчитала суммы всех чисел, которые больше 10.
Второй метод: использование горячих клавиш табличного редактора
Все вышеприведенные манипуляции можно реализовать при помощи специальных горячих клавиш табличного редактора. Подробная инструкция выглядит так:
- Реализуем копирование нужного диапазона при помощи комбинации клавиш «Ctrl+C».
- Сюда же осуществляем обратную вставку при помощи комбинации клавиш «Ctrl+V».
- Щелкаем на «Ctrl», чтобы отобразить небольшое меню, в котором будут предложены вариации вставки.
- Щёлкаем на «З» на русской раскладке или же используем стрелочки для выбора параметра «Значения». Осуществляем подтверждение всех проделанных действий при помощи клавиши «Enter», расположенной на клавиатуре.