Все формулы в Excel начинаются со знака равенства «=». Они оперируют с числами, текстом, названиями ячеек, функциями. Главные отличия от обычных математических формул – формула в Excel задается одной строкой, вместо переменных используются названия ячеек. Для вычислений применяются следующие операции :
- сложение – «+»;
- вычитание – «-»;
- умножение – «*»;
- деление – «/»;
- возведение в степень – «^».
Для указания очередности действий используются скобки . Вот образцы корректных формул:
Пример простейших вычислений:
Применение функций
Для автоматизации расчетов применяются разнообразные функции . Их можно вставлять нажатием кнопки «Вставить функцию ». Альтернативный вариант – нажатие комбинации Shift+ F3 (для ноутбуков Shift+ Fn+ F3 ). Появляется диалоговое окно, в котором надо выбрать категорию. Далее определяется конкретная функция, задаются ее аргументы, нажимается «ОК ».
Вот пример пошагового вычисления
квадратного корня числа. Вызвали диалоговое окно, выбрали раздел «Математические
», далее «КОРЕНЬ
»:
Задали аргумент
(в данном случае это B1):
Нажали «ОК
»:
Математические вычисления
Одна из самых востребованных математических функций – СУММ , она предназначена для суммирования значений ячеек. СУММ может работать с целым диапазоном :
СУММ(B1:B3)
Здесь сложили B1, B2 и B3.
Также применяется суммирование отдельных ячеек:
СУММ(B1;B3)
А здесь сложили B1 и B3.
Аналог СУММ – ПРОИЗВЕД , позволяет перемножать значения. Пример перемножения диапазона:
ПРОИЗВЕД(B1:B3)
Логические функции
В разделе «Логические » есть средства для работы с логическими значениями. Самый простой вариант – присвоить ячейке значение ИСТИНА или ЛОЖЬ :
Можно инвертировать содержимое:
ЕСЛИ позволяет выстраивать сложные конструкции. Применяется в таком формате:
ЕСЛИ(логическое_выражение;значение1;значение2)
В качестве логического выражения можно использовать одну из операций сравнения:
- равно – «=»;
- больше – «>»;
- меньше – «<»;
- больше или равно – «>=»;
- меньше или равно – «<=»;
- не равно – «<>».
Примеры операций сравнения: 2<3, B1<>B4, F5>=10.
Пример использования ЕСЛИ:
ЕСЛИ(2>=1;10;20)
Обработка текста
В Excel имеются средства и для несложных операций с текстовыми величинами. ДЛСТР возвращает длину текстового аргумента, например, =ДЛСТР(«Волга впадает в Каспийское море») даст результат 31.
НАЙТИ осуществляет поиск одного текста в другом и возвращает номер позиции первого вхождения. Если ввести =НАЙТИ(«ас»;»Василий»;1), то получим 2.
ПОДСТАВИТЬ заменяет в тексте один фрагмент другим. =ПОДСТАВИТЬ(«Все нормально!»;»е»;»ё») даст результат «Всё нормально!». Если в качестве третьего аргумента указать пустую строку, то фрагмент будет просто удален из всего текста.
Чтобы объединить несколько строк в одну, можно использовать СЦЕПИТЬ . =СЦЕПИТЬ(«Добрый «;»день») создаст небольшую фразу из двух слов.
Дата и время
В Excel много удобных средств для обработки времени и дат. В приведенном ниже примере в A1 поместили текущую дату с помощью формулы =СЕГОДНЯ()
, потом разбили ее на составные части. Для этого применили конструкции
=ДЕНЬ(A1), =МЕСЯЦ(A1), =ГОД(A1). Результат:
Чтобы получить текущее время, наберите =ТДАТА() , затем измените формат ячейки правой кнопкой мыши («Формат ячеек…» -> «Число» -> «Время»), выберите удобное представление. Из текущего времени также можно выделить составные части (используя СЕКУНДЫ, МИНУТЫ, ЧАСЫ).
Все встроенные функции Excel разделены на несколько категорий. Например, функции категории Текстовые используются, в основном, для работы с текстовыми строками. Математические функции, с помощью которых можно составлять различные математические выражения, отнесены к категории Математические и т.п. Основные категории функций перечислены ниже. Все названия категорий соответствуют названиям команд, расположенным в группе Библиотека функций на вкладке Формулы.
Логические функции
Категория Логические содержит семь функций, в том числе функции ЕСЛИ и ЕСЛИОШИБКА. Использование логических функций делает формулы более гибкими, а использование функции ЕСЛИ наделяет формулу способностью «принимать решения». Благодаря этому функция ЕСЛИ стала самой используемой логической функцией. Функция ЕСЛИОШИБКА имеется в библиотеке встроенных функций только в Excel 2010 (2007). Об этом необходимо помнить, если ваши рабочие книги используются в разных версиях Excel.
Текстовые функции
Текстовые функции предназначены для обработки текста, например если создаете информационную базу про бесплатные программы скачать . Например, с помощью функций ПРОПНАЧ или ДЛСТР можно изменить регистр или определить длину текстовой строки. Используя текстовые функции, можно объединить несколько строк в одну или, наоборот, разделить одну текстовую строку на несколько строк. Например, формула =СЦЕПИТЬ(A1;A2) объединяет две текстовые строки, содержащиеся в ячейках A1 и A2, в одну.
Функции категории Проверка свойств и значений
Функции этой категории часто называют информационными. Функция ЯЧЕЙКА этой категории позволяет получить информацию о ячейке. Другие информационные функции проверяют выполнение какоголибо условия и, в зависимости от результата, возвращают значение ИСТИНА или ЛОЖЬ (или числовое значение). Например, с помощью функции ЕЧИСЛО можно проверить, данные какого типа содержит ячейка. Если в ячейке содержится число, функция ЕЧИСЛО возвращает логическое значение ИСТИНА, в противном случае функция возвращает логическое значение ЛОЖЬ.
Функции Дата и время
Функции, принадлежащие к этой категории, предназначены для работы со значениями даты и времени. По сути, эти функции работают с числовыми значениями, потому что дата и время в Excel являются числами, к которым применен один из числовых форматов даты и времени. С помощью функции этой категории можно вычислить количество рабочих дней между двумя датами (функция ЧИСТРАБДНИ), преобразовать дату в год (функция ГОД), месяц (функция МЕСЯЦ) или день недели (функция ДЕНЬНЕД) и т.п.
Математические функции
Математические функции позволяют выполнять простые и сложные вычисления. В категорию Математические входят тригонометрические функции, например SIN, COS, ACOS; функции, выполняющие арифметические действия, например СУММ, ПРОИЗВЕД, ЧАСТНОЕ; и многие другие функции. К этой же категории относятся функции, позволяющие работать с массивами значений или матрицами, - МУМНОЖ, МОПРЕД и МОБР, а также функции АГРЕГАТ и ПРОМЕЖУТОЧНЫЕ.ИТОГИ, которые используются для получения итоговых значений (суммы, среднего арифметического, минимального или максимального значений и т.п.) в массивах данных или списках. Функция АГРЕГАТ доступна только в Excel 2010!
Функция Excel - это заранее определенная формула, которая работает с одним или несколькими значениями и возвращает результат.
Наиболее распространенные функции Excel являются краткой записью часто используемых формул.
Например функция =СУММ(А1:А4) аналогична записи =А1+А2+А3+А4.
А некоторые функции выполняют очень сложные вычисления.
Каждая функция состоит из имени и аргумента .
В предыдущем случае СУММ - это имя функции , а А1:А4 - аргумент . Аргумент заключается в круглые скобки.
Т.к. функция суммы используется наиболее часто, то на панель инструментов "Стандартная" вынесена кнопка "Автосумма".
Введите в ячейки А1, А2, А3 произвольные числа. Активизируйте ячейку А4 и нажмите кнопку автосуммы. Результат показан ниже.
Нажмите клавишу ввода. В ячейку А4 будет вставлена формула суммы ячеек А1..А3. Кнопка автосуммы снабжена выпадающим списком, из которого можно выбрать другую формулу для ячейки.
Для выбора функции служит кнопка "Вставка функции" в строке формул. При ее нажатии появляется следующее окно.
Если точно неизвестна функция, которую надо применить в данный момент, то в окне диалога "Поиск функции" можно произвести поиск.
Если формула очень громоздкая, то можно включить в текст формулы пробелы или разрывы строк. Это никак не влияет на результаты вычислений. Для разрыва строки надо нажать комбинацию клавиш Alt+Enter.
Можно использовать в формулах вместо ссылок на ячейки таблицы заголовки таблицы. Постройте следующий пример.
По умолчанию Microsoft Excel не распознает заголовки в формулах. Чтобы использовать заголовки в формулах, выберите команду Параметры в меню Сервис. На вкладке Вычисления в группе Параметры книги установите флажок Допускать названия диапазонов.
При обычной записи формула в ячейке В6 выглядела бы так: =СУММ(В2:В4).
При использовании заголовков формула будет выглядеть так: =СУММ(Кв 1).
Необходимо знать следующее:
- Если формула содержит заголовок столбца/строки, в котором она находится, то Excel считает, что вы хотите использовать диапазон ячеек, расположенных ниже заголовка столбца таблицы (или справа от заголовка строки);
- Если формула содержит заголовок столбца/строки, отличного от того, в котором она находится, Excel считает, что вы хотите использовать ячейку на пересечении столбца/строки с таким заголовком и строки/столбца, где расположена формула.
- При использовании заголовков можно указать любую ячейку таблицы с помощью пересечения диапазонов. Например, для ссылки на ячейку С3 в нашем примере можно использовать формулу =Строка2 Кв 2 . Обратите внимание на пробел между заголовками строки и столбца.
- Формулы, содержащие заголовки, можно копировать и вставлять, при этом Excel автоматически настраивает их на нужные столбцы и строки. Если будет произведена попытка скопировать формулу в неподходящее место, то Excel сообщит об этом, а в ячейке выведет значение ИМЯ? . При смене названий заголовков, аналогичные изменения происходят и в формулах.
Для того чтобы понять как пользоваться этой программой, необходимо рассмотреть формулы EXCEL с примерами.
Если поставить курсор мышки на любую ячейку и нажать на пункт «выбрать функцию», то появляется мастер функций.
С его помощью можно найти необходимую формулу максимально быстро. Для этого можно ввести ее название, воспользоваться категорией.
Программа Excel очень удобна и проста в использовании. Все функции разделены по категориям. Если категория необходимой функции известна, то ее отбор осуществляется по ней.
В случае если функция неизвестна пользователю, то он может установить категорию «полный алфавитный перечень».
Например, дана задача, найти функцию СУММЕСЛИМН. Для этого нужно зайти в категорию математических функций и там найти нужную.
Функция ВПР
С помощью функции ВПР можно извлечь необходимую информацию из таблиц. Сущность вертикального просмотра заключается в поиске значения в крайнем левом столбце заданного диапазона.
После чего осуществляется возврат итогового значения из ячейки, которая располагается на пересечении выбранной строчки и столбца.
Вычисление ВПР можно проследить на примере, в котором приведен список из фамилий . Задача – по предложенному номеру найти фамилию.
Применение функции ВПР
Формула показывает, что первым аргументом функции является ячейка С1.
Второй аргумент А1:В10 – это диапазон, в котором осуществляется поиск.
Третий аргумент – это порядковый номер столбца, из которого следует возвратить результат.
Вычисление заданной фамилии с помощью функции ВПР
Кроме того, выполнить поиск фамилии можно даже в том случае, если некоторые порядковые номера пропущены.
Если попробовать найти фамилию из несуществующего номера, то формула не выдаст ошибку, а даст правильный результат.
Поиск фамилии с пропущенными номерами
Объясняется такое явление тем, что функция ВПР обладает четвертым аргументом, с помощью которого можно задать интервальный просмотр.
Он имеет только два значения – «ложь» или «истина». Если аргумент не задается, то он устанавливается по умолчанию в позиции «истина».
Округление чисел с помощью функций
Функции программы позволяют произвести точное округление любого дробного числа в большую или меньшую сторону.
А полученное значение можно использовать при расчетах в других формулах.
Округление числа осуществляется с помощью формулы «ОКРУГЛВВЕРХ». Для этого нужно заполнить ячейку.
Первый аргумент – 76,375, а второй – 0.
Округление числа с помощью формулы
В данном случае округление числа произошло в большую сторону. Чтобы округлить значение в меньшую сторону, следует выбрать функцию «ОКРУГЛВНИЗ».
Округление происходит до целого числа. В нашем случае до 77 или 76.
В программе Excel помогают упростить любые вычисления. С помощью электронной таблицы можно выполнить задания по высшей математике.
Наиболее активно программу используют проектировщики, предприниматели, а также студенты.
Вся правда о формулах программы Microsoft Excel 2007
Формулы EXCEL с примерами - Инструкция по применению
Функция – это специальная, заранее созданная, формула для сложных вычислений, в которую пользователю следует ввести лишь необходимые аргументы. Она имеет имя, описывающее ее предназначение (например, СУММ – это сложение нескольких чисел) и аргументы. Последних может быть несколько, но они всегда заключаются в круглые скобки. Далее мы рассмотрим основные функции Эксель и кратко расскажем об их предназначении.
Самые популярные функции
- СУММ – используется в Эксель для сложения значений в полях.
- ЕСЛИ – позволяет вернуть разные значения в зависимости от соблюдаемых условий.
- ПРОСМОТР – необходима для поиска значений, находящихся на идентичной позиции, но в другом столбце или строчке.
- ВПР – задействуется для поиска информации во всей таблице Эксель или её отдельных диапазонах. С её помощью легко отыскать номер телефона по фамилии либо, наоборот (по подобию телефонной книги).
- ПОИСКПОЗ – применяется для поиска заданного элемента и возвращения его относительной позиции в диапазон.
- ВЫБОР – предназначена для выбора значений из списка.
- ДАТА – может вернуть определенному периоду его порядковый номер.
- ДНИ – позволяет возвратить число дней между заданными датами.
- НАЙТИБ, НАЙТИ – обнаруживают вхождение текста в другую текстовую строку.
- ИНДЕКС – находит в диапазоне или таблице Эксель значение или ссылку на него и возвращает обратно.
Как вводить функции в таблицу
Добавление необходимого значения на рабочий лист можно выполнять непосредственно с клавиатуры либо через специальную команду программы Эксель, находящуюся в меню «Вставка». После выделения ячейки и выбора пункта «Вставка/Функция» на экране появится диалоговое окно Мастера.
Для начала вам следует выбрать категорию, а после функцию Эксель из алфавитного списка. Программа введет в ячейку знак равенства, название, круглые скобки и откроет следующее диалоговое окно. В него следует внести аргументы (координаты ячеек, в которых располагаются необходимые данные). После нажатия на кнопку «Ок» или Enter на клавиатуре, полученная функция появится в строчке с формулами.
