Программное обеспечение Microsoft Office 07.12.2019 6410
Microsoft Excel облегчает жизнь тем, кто работает в сфере информационных технологий. Электронные таблицы, благодаря своей огромной функциональности, позволяют выполнять самые сложные вычисления, анализировать их и строить диаграммы. Использование различных типов формул позволяет работать с константами, операторами, ссылками, текстом, функциями и т.д. Давайте обсудим, как создать формулу в Excel, а также подробно разберем конкретные примеры.
Как создать формулу в Excel?
Таблицы могут помочь вам выполнять широкий спектр вычислений и других операций. Однако лучше начать с простого, потому что, не поработав предварительно с программой, не каждый сразу поймет, что делать для достижения желаемого результата. Рассмотрим подробно и пошагово, как создать формулу в Excel:
- Запускаем Excel с рабочего стола или из меню.
- Откроется окно с множеством ячеек. Именно в них и нужно вводить формулы. Каждая ячейка имеет свой уникальный адрес, состоящий из номера столбца и буквы строки. Он также отображается в специальном поле над таблицей, если вы поместите курсор на интересующую ячейку. На экране активная ячейка выделена черным цветом, ее адрес E5.
- Следующим шагом будет создание таблицы в Excel. Вносим туда исходные данные, необходимые для расчетов. В нашем случае — доходы и расходы на конкретную дату. Необходимо рассчитывать прибыль за каждый день (вычитать расходы из доходов).
- Щелкните левой кнопкой мыши по ячейке, в которой вы ожидаете увидеть результат. В этом случае мы рассчитываем прибыль за первый день, адрес ячейки D5.
- На клавиатуре нажмите знак равенства «=».
- Щелкните левой кнопкой мыши ячейку дохода (B5). Он будет выделен цветом, а его адрес появится после знака равенства.
- Курсор находится в ячейке, в которую мы вводим формулу. Нажмите знак минуса «-» на клавиатуре «-«.
- Щелкните левой кнопкой мыши по ячейке с расходами (C5).
- Нажмите Enter на клавиатуре и посмотрите результат.
- В примере необходимо рассчитать прибыль за несколько дней. Конечно, вам не нужно каждый раз заново вводить формулу в Excel. Вы можете сделать это проще. Щелкаем по ячейке, в которой уже была введена формула, перемещаем курсор мыши в правый нижний угол — смотрим, чтобы он превратился в знак плюса.
- Нажмите и удерживайте левую кнопку мыши, одновременно выбирая нужные ячейки (как если бы вы растягивали формулу).
- Отпустив кнопку мыши, мы увидим, что формула автоматически скопировалась в ячейки, где сразу же отобразились результаты расчета.
- Как видно из примера, ничего сложного нет и рассмотренный способ ввода формулы — не единственно возможный вариант. Есть другой способ: вверху экрана (над ячейками) есть специальная строка, в которую также можно вводить формулы. При этом следует учитывать, что совсем не обязательно щелкать по ячейкам, которые участвуют в формуле, достаточно указать их адреса.
Совет: Иногда при работе с большим объемом данных в табличной форме становится неудобно постоянно возвращаться к началу, чтобы увидеть заголовок. В этом случае в Excel лучше «заморозить» строку — данные потекут, а заголовок таблицы останется на месте.
Примеры написания формул в Экселе
Использование математических, логических, финансовых и других функций позволяет пользователям выполнять огромное количество вычислений. Таблицы значительно облегчают жизнь студентам, инженерам, экономистам, маркетологам и т.д. Список формул, доступных в Excel, можно просмотреть несколькими способами:
- с помощью кнопки «Вставить функцию», расположенную над рабочим полем;
- перейдя на вкладку «Формулы» в меню и найдя там «Математик» (логика, текст и т д).
- нажав «Вставить функцию» на вкладке «Формулы».
В Excel есть много разных операторов и функций — давайте подробнее рассмотрим самые популярные.
СУММ — суммирование чисел
Практически каждому нужно что-то просуммировать — поэтому оператор СУММ используется в Excel чаще других. Алгоритм действий при необходимости сложения чисел:
- Щелкните ячейку, в которой вы ожидаете получить результат. Перейдите на вкладку «Формулы» в меню, нажмите «Математика». Найдите функцию СУММ в раскрывающемся списке и щелкните по ней левой кнопкой мыши.
- В появившемся окне нужно ввести аргументы функции. Здесь можно пойти несколькими способами: выбрать нужные ячейки или диапазон или ввести их адреса вручную. При этом следует учитывать, что ячейки перечисляются через точку с запятой (например, A1; A3; A4), а диапазон в формулах обозначается двоеточием (например, B4: B10).
- Нажмите «ОК» и посмотрите результат суммирования. Обратите внимание, что в строке формул отображается функция и ее аргументы.
Синтаксис функции: = СУММ (число1; число2; число3;…) или = СУММ (число1: числоN).
СУММЕСЛИ — суммирование при соблюдении заданного условия
Отличный оператор, позволяющий быстро добавлять числа в ячейки, если выполняется условие. Например, вам нужно добавить только положительные числа или узнать общую зарплату продавцов без учета менеджеров и других сотрудников. Рассмотрим пример, в котором вам нужно рассчитать зарплату для каждой позиции в Excel:
- На вкладке «Формулы» нажмите «Математика» и выберите СУММЕСЛИМН из списка.
- Выберите диапазон для суммы — для этого нужно нажать на соответствующую строку, затем выбрать ячейки с зарплатой (диапазон E4: E13).
- Определите диапазон, из которого мы возьмем ячейки, соответствующие условию. В нашем случае мы выбираем ячейки с локациями. В строке «Критерий» пишем — «продавец», то есть рассчитываем зарплаты всех продавцов.
- В результате оператор СУММЕСЛИ суммирует ячейки только с зарплатами продавцов.
Конечно, условия могут быть разными, все зависит от конкретной ситуации и потребностей пользователя.
Синтаксис функции: = СУММЕСЛИ (диапазон, критерий, диапазон_суммы).
СТЕПЕНЬ — возведение в степень
Часто необходимо до некоторой степени увеличить число, поэтому следует использовать функцию ГРАДУСЫ:
- Нажмите на «математические» формулы в соответствующей вкладке и найдите СТЕПЕНЬ.
- В открывшемся окне введите аргументы функции: число — это основание (то, что мы возводим в степень), степень — это показатель степени.
Аргументы записываются как вручную с клавиатуры, так и при нажатии на ячейки, если они были предварительно заполнены.
Синтаксис функции: = ГРАДУСЫ (число; градус).
СЛУЧМЕЖДУ — вывод случайного числа в интервале
Эта функция возвращает случайное число между двумя определенными. Неплохая альтернатива обычному сервису. Мы действуем следующим образом:
- В «Математических формулах» выберите СЛУЧАЙНО.
- Аргументами функции являются нижняя и верхняя границы: мы вводим их, нажимая на ячейки.
- В результате мы получаем случайное число между двумя данными.
Синтаксис функции: = СЛУЧМЕЖДУ (нижний_ предел; верхний предел).
ВПР — поиск элемента в таблице
Функция, которая значительно экономит время, помогая вам находить данные. Это относится к ссылочным операторам. Вы можете использовать его в разных ситуациях, например вам нужно найти код сотрудника с именем сотрудника:
- На вкладке «Формулы» щелкните «Связи и массивы» и выберите ВПР из списка.
- В поле «Значение поиска» пишем, что ищем (в нашем случае полное имя сотрудника). В аргументе функции «Таблица» нужно указать область поиска (выделить всю таблицу). В «Номер столбца» указываем, из какого столбца нужно возвращать результат (зависимый код). В режиме просмотра интервалов введите FALSE, если требуется точное совпадение.
- Это позволяет быстро и легко искать в больших таблицах.
Синтаксис функции: ВПР (lookup_value, table, column_num, range_lookup).
СРЗНАЧ — возвращение среднего значения аргументов
Статистическая функция, позволяющая вычислить среднее арифметическое. Допустим, вы хотите узнать среднюю зарплату в компании:
- На вкладке «Формулы» щелкните «Дополнительные функции» и выберите «Статистика». В появившемся списке найдите СРЕДНЕЕ.
- В «Аргументах функций» необходимо указать зарплату каждого сотрудника, выделив соответствующие ячейки в таблице. Вы также можете ввести диапазон вручную.
- Функция вернет среднее арифметическое аргументов, которое будет средней зарплатой в компании.
Синтаксис функции: = СРЕДНЕЕ (число1; число2;).
МАКС — определение наибольшего значения из набора
Необходимость быстро найти наибольшее значение из любой выборки возникает довольно часто. В этом случае поможет функция статистики MAX. Допустим, нам нужно узнать, какое максимальное количество посетителей было на сайте за определенный период:
- Щелкните ячейку, в которой будет отображаться результат. На вкладке «Формулы» нажмите «Вставить функцию», в «Категории» выберите «Статистика» и в появившемся списке — МАКС.
- В «Аргументах функции» нужно указать диапазон, из которого будет выбрано максимальное число. В нашем случае ячейки с количеством посетителей. Нажмите «Число 1» и выберите нужные ячейки (конечно, вы можете ввести диапазон с клавиатуры).
- определено максимальное количество посетителей.
Синтаксис функции: = МАКС (число1; число2;…).
КОРРЕЛ — коэффициент корреляции
Чрезвычайно полезная и удобная функция, позволяющая определять и оценивать взаимосвязь между наборами данных. Например, у нас есть информация о количестве посетителей сайта за каждый день и данные о показах рекламы в это время. Определяем, как реклама влияет на количество гостей:
- Во вкладке «Формулы» нажимаем «Вставить функцию», в «Категории» переходим в «Статистика» и находим КОРРЕЛЯЦИЮ в списке.
- Заполняем «Массивы функций»: в качестве «Массив 1» выбираем ячейки с количеством посетителей, в «Массив 2» вставляем данные о показе рекламы.
- В результате функция КОРРЕЛЬ возвращает значение коэффициента корреляции, который показывает наличие или отсутствие зависимости двух значений друг от друга. В этом примере это 0,7261, что означает довольно тесную связь между количеством посетителей веб-сайта и количеством показов рекламы.
Чтобы получить еще больше информации о взаимосвязи между случайными величинами, вы можете выполнить регрессионный анализ, на основе которого вы сможете построить график функции в Excel или создать диаграмму в Word.
Синтаксис функции: = КОРРЕЛ (массив1; массив2).
ДНИ — количество дней между двумя датами
Функция, с помощью которой в Excel можно быстро узнать количество дней между двумя известными датами. Например, сотрудник уходит в отпуск: есть две даты, которые определяют продолжительность отпуска. Вам нужно рассчитать, сколько дней длится праздник:
- На вкладке «Формулы» нажмите кнопку «Дата и время» и выберите функцию ДНИ из списка.
- В поле «Конечная_дата» введите дату окончания выходного, щелкнув соответствующую ячейку, а в «Начальная_дата» — дату его начала.
- В итоге получаем продолжительность отпуска в днях.
Синтаксис функции: = ДНИ (конечная_дата; начальная_дата).
ЕСЛИ — выполнение условия
Эта функция в Excel предназначена для проверки определенного условия. Например, у компании есть план продаж на каждый день и сотрудники, которые ведут статистику общего объема продаж для каждого из них. Определите, кто хорошо сработал и выполнил план:
- На вкладке «Формулы» нажмите «Логические» и найдите ЕСЛИ.
- Заполним «Аргументы функции». В поле «Log_Expression» нужно написать условие: в нашем случае выручка от продаж (B5) должна быть больше 40 000 руб. В «Value_if_true» мы пишем, что будет отображаться в ячейке при выполнении условия. Следовательно, если выручка превышает 40 000 рублей, «план выполнен». В «Value_if_false» указываем «План не выполнен» — это предложение появится в ячейке, если доход продавца меньше 40 000 руб.
- Распространяем формулу на остальные ячейки и получаем результат по выполнению плана для всех сотрудников.
Синтаксис функции: = ЕСЛИ (логическое_выражение; значение_если_ истинное значение; значение_если_ ложь).
СЦЕПИТЬ — объединение текстовых строк
Формула относится к тексту и предназначена для объединения нескольких строк в одну. Часто используется в Excel для объединения двух или более ячеек. Например, фамилии, имена и отчества людей записываются в разных ячейках, и возникла необходимость создать сводный столбец. Мы действуем:
- Переходим на вкладку «Формулы» и нажимаем «Текст». Найдите ЦЕПЬ в списке.
- В «Аргументах функций» мы последовательно указываем объединяемые ячейки. Вы можете щелкнуть по ним или вручную ввести буквенно-цифровое обозначение.
- Результат, конечно, далек от идеала: между словами нет пробелов.
- Решить эту проблему не так уж и сложно — некоторые рекомендуют просто добавить пробел после текста в каждой ячейке, однако, если информации много, вы не хотите добавлять лишние символы. Лучше пойти другим путем: поправить формулу. Для этого в синтаксисе функции нужно добавить пробелы в кавычках — » «.
- Появились зазоры, проблем больше нет. Распространите формулу на другие ячейки.
Синтаксис функции: = СЦЕПИТЬ (текст1; текст2;…).
ЛЕВСИМВ — возвращает заданное количество символов
Удобная функция в Excel, которая очень помогает в работе с текстом, возвращая определенное количество символов из начала строки. Например, у статей есть заголовки, но иногда они довольно длинные, что ухудшает восприятие. Если вы определите, что заголовок должен состоять из 70 символов, вы можете узнать, на что похожи заголовки, и исправить их:
- В «Формулах» щелкните «Текст» и обратитесь к функции ВЛЕВО.
- В поле «Текст» введите нужную ячейку, в поле «Количество знаков» укажите желаемую длину заголовка.
- Растягиваем формулу на другие ячейки и получаем результат: текст ограничен указанным количеством символов.
Синтаксис функции: = LEFT (текст; число_символов).
Подводим итоги
Excel позволяет решать множество различных задач, будь то вычитание процента от суммы или проведение регрессионного анализа наборов данных. Одновременно управлять огромным количеством функций непросто, но ни одна из них не заставляет вас осваивать программу как внутри, так и снаружи. Действовать согласно поставленным целям вполне реально. В Excel есть справочные материалы по каждой формуле, которые лучше всего прочитать перед началом работы.
