Меню
ГлавнаяТехника

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

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

Для этого нужно сделать следующие шаги.

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

  1. Затем выберите желаемую функцию. В качестве примера рассмотрим «СЧЁТЕСЛИ». Сразу после этого вы увидите короткую информацию о выбранном пункте. Для подробной справки нужно будет кликнуть на указанную функцию. Для продолжения необходимо нажать на «OK».

  1. После этого вас попросят указать «Аргументы функции». Поля будут всегда меняться в зависимости от выбранной формулы. В данном случае нужно указать диапазон ячеек и критерий для подсчета.

  1. Перейдите к первому полю. Выделите нужное количество клеток.

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

Обратите внимание: все аргументы должны быть заполнены полностью. Формула не будет работать, если одно из полей окажется пустым.

После этого нажмите на «OK».

  1. Благодаря этому вы увидите какое-нибудь число. Этому значению будет соответствовать количество тех ячеек, которые удовлетворяют вашему критерию. В данном случае мы выделили 14 пустых ячеек.

  1. Если внести какие-нибудь изменения, то результат функции изменится мгновенно.

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

Если данная строка вам кажется маленькой и неудобной, нужно нажать на горячие клавиши Ctrl +Shift +U . Благодаря этому её высота увеличится в несколько раз.

Для возврата к прежнему режиму нужно повторить комбинацию клавиш Ctrl +Shift +U .

Размер строки формул останется неизменным даже после закрытия программы Эксель. Настройка будет работать при каждом запуске и дальше. Она меняется только вручную.

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

СЧЁТЕСЛИ(C3:C16;””)

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

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

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

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

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

  1. Перейдите в первую клетку в этой таблице. Вызовите окно «Вставка функции». Выберите категорию «Математические». Найдите там пункт «ОКРУГЛ» и кликните на «ОК».

  1. Укажите адрес ячейки, в которой расположено ваше число. Затем заполните поле «Число_разрядов». Оно определяет количество десятичных разрядов после запятой. Для сохранения кликните на «ОК».

  1. Благодаря этому вы увидите следующий результат.

  1. Затем наведите курсор на правый нижний угол этой ячейки. У вас должен будет измениться внешний вид указателя мыши. Сделайте левый клик и, не отпуская пальца, проведите до конца таблицы.

  1. В итоге вы увидите следующее.

  1. Повторите описанные выше действия для остальных функций.

Данные функции имеют следующее назначение:

  • ОКРУГЛ – округление указанной цифры до определенного количества знаков после запятой. Принцип работы точно такой же, как учат округлять в школе;
  • ОКРУГЛВНИЗ – округление до ближайшего (по модулю) меньшего значения. При этом все остальные знаки после указанной точности отбрасываются. В нашем случае из 1,598 стало просто 1,59. Хотя по правилам математики должно быть 1,6;
  • ОКРУГЛВВЕРХ – округление до ближайшего (по модулю) большего значения. Принцип работы точно такой же, как и у «ОКРУГЛВНИЗ»;
  • ОКРУГЛТ – округление числа до ближайшего кратного значения, которое кратно тому, что указано в поле «точность». В нашей таблице все результаты кратны числу 2. Именно оно было указано во втором параметре;
  • ОКРВВЕРХ – принцип работы точно такой же, как и у функции «ОКРУГЛТ». Только в этом случае округление происходит до ближайшего большего, а не любого кратного;
  • ОКРВНИЗ – то же самое, только в меньшую сторону;
  • ОТБР – данная функция отбрасывает всю дробную часть вплоть до указанного количества знаков;
  • ЦЕЛОЕ – округление до ближайшего наименьшего числа. При этом остается только целая часть;
  • ЧЁТН – функция возвращает ближайшее четное целое число;
  • НЕЧЁТ — функция возвращает ближайшее нечетное целое число.

Как видите, многие формулы являются аналогами друг друга.

У функций, которые оканчиваются на «МАТ», существует третий дополнительный параметр. Если его не указывать, то принцип работы точно такой же, как и у функций без приставки «МАТ».

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

Данные функции позволяют (синтаксис приводится именно для нашей таблицы):

  • СУММ – посчитать сумму всех ячеек, которые входят в указанный диапазон;
=СУММ(C4:C16)
  • СУММЕСЛИ – посчитать сумму всех ячеек, которые входят в указанный диапазон и выполняют определенное условие;
=СУММЕСЛИ(C4:C16;»>3")
  • СУММЕСЛИМН – посчитать сумму всех ячеек, которые входят в указанный диапазон и выполняют несколько определенных условий;
=СУММЕСЛИМН(C4:C16;C4:C16;»>3";C4:C16;»<7")
  • СУММПРОИЗВ – посчитать произведение ячеек с каждой строки указанного диапазона.
=СУММПРОИЗВ(B4:B16;C4:C16)
  • СУММКВ – вычислить сумму квадратов указанных аргументов. Можно использовать как простые числа, так и большие массивы.
=СУММКВ(B4:B16;C4:C16)
  • СУММРАЗНКВ – посчитать сумму разностей квадратов указанных массивов. Используется следующая формула.

=СУММРАЗНКВ(B4:B16;C4:C16)
  • СУММСУММКВ – вычислить сумму сумм квадратов указанных массивов. Используется следующая формула.

=СУММСУММКВ(B4:B16;C4:C16)
  • СУММКВРАЗН – посчитать сумму квадратов разностей указанных массивов данных. При подсчетах используется следующая формула.

=СУММКВРАЗН(B4:B16;C4:C16)

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

Результат будет следующим.

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

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

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

В качестве примера за аргумент X возьмем 1 элемент 1-го массива. Степень N будет начинаться с 1 с дальнейшим шагом 1. В роли коэффициентов возьмем все ячейки второго массива.

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

В данном случае всё намного проще. Здесь используются стандартные школьные тригонометрические формулы. И называться они будут почти точно так же. К ним относятся:

  • COS – косинус угла;
  • COSH – гиперболический косинус угла;
  • COT – котангенс угла;
  • COTH – гиперболический котангенс угла;
  • CSC – косеканс угла;
  • CSCH – гиперболический косеканс угла;
  • SEC – секанс угла;
  • SECH – гиперболический секанс угла;
  • SIN – синус угла;
  • SINH – гиперболический синус угла;
  • TAN – тангенс угла;
  • TANH – гиперболический тангенс угла.

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

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

=COS(РАДИАНЫ(C2))

Благодаря описанным выше функциям можно построить следующую таблицу.

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

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

Вот так выглядит таблица в учебниках по математике. Эксель корни не выводит – он сразу считает конечное числовое значение.

Кроме этого есть и обратные функции. А именно:

  • ACOS – арккосинус числа;
  • ACOSH – гиперболический арккосинус числа;
  • ACOT – арккотангенс числа (работает с 2013 года);
  • ACOTH – гиперболический арккотангенс числа (работает с 2013 года);
  • ASIN – арксинус числа;
  • ASINH – гиперболический арксинус числа;
  • ATAN – арктангенс числа;
  • ATANH – гиперболический арктангенс числа.

Для корректного отображения результата нужно использовать функцию «ГРАДУСЫ». Результат будет следующим. Его вы тоже можете проверить на совпадение с табличными величинами. Проверка покажет, что автозаполнение данных прошло корректно.

В некоторых случаях мы видим ошибку «#ЧИСЛО». Это следствие того, что этих значений не существует. Например, в формуле ACOSH может использоваться число больше или равное 1. А в нашей таблице происходит разбор значений начиная с -1 – это неприемлемо в данной функции. Где-то наоборот – диапазон значений может находиться от -1 до 1, а не больше. То есть все наши эмпирические результаты рассматриваются с учетом правил математики.

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

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

  1. Строим таблицу и указываем необходимые данные. Вы можете заполнить её любыми цифрами. Это ничего критичного значить не будет.

  1. Вставляем в первую ячейку второго столбика следующую формулу.
=EXP(B4)

Затем дублируем её в остальные клетки (тянем за уголок первого результата).

  1. Затем выделите все ваши значения в таблице (выделение заголовка не запрещается). Перейдите на вкладку «Вставка» и кликните на иконку «График». Выберите любой из предложенных вариантов макетов.

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

  1. Сделайте правый клик по диаграмме. В появившемся меню нажмите на пункт «Выбрать данные».

  1. Кликните на кнопку «Изменить».

  1. В появившемся окне нужно будет задать необходимый нам диапазон ячеек. Для продолжения нажмите на «OK».

  1. Затем то же самое.

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

Как видите, рост функции совсем не линейный – он увеличивается очень быстро. Маленький прирост наблюдается только вначале. Строить такой график довольно просто.

Для демонстрации этих формул нужно добавить еще одну таблицу.

При помощи функции «РИМСКОЕ» мы смогли записать эти цифры в виде римских чисел. Затем используя «АРАБСКОЕ», смогли вернуть нормальный для нас вид. Причем замена происходила с предыдущего преобразования. Для программы Excel неважно, что будет содержать эта ячейка – формулу, статический или переменный текст. Именно поэтому практический потенциал функций просто невероятен.

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

  • ГРАДУСЫ – перевод радиан в градусы;
  • РАДИАНЫ – перевод градусов в радианы.

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

Затем нужно будет ввести следующие формулы.

=ДЕС(C6;16) =ДЕС(C9;2)

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

Арифметические функции

К данным формулам относятся:

  • ПРОИЗВЕД – умножение двух чисел;
  • ОСТАТ – удаление целой части от деления;
  • СТЕПЕНЬ – возведение указанного числа в нужную степень;
  • ЧАСТНОЕ – получение целой части от деления числа;
  • EXP – возведение экспоненты в указанную степень;
  • КОРЕНЬ – получение положительного корня от указанного числа.

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

Для простого вычитания двух значений достаточно использовать обычный символ минус («-»). Для этого специальной функции не существует.

Разное

  • Кроме этого есть и множество других математических функций, которые сложно объединить в одну условную группу.
  • ABS – модуль указанного значения;
  • АГРЕГАТ – агрегированное выражение списка (более подробно смотрите в официальной справке);
  • ФАКТР – расчёт факториала указанного числа;
  • НОД – поиск наибольшего общего делителя;
  • НОК – поиск наименьшего общего кратного;
  • МОПРЕД – позволяет найти определитель матрицы массива данных;
  • МУМНОЖ – матричное произведение чисел двух массивов;
  • ПИ – ввод в формулу числа «пи»;
  • СЛЧИС – случайный выбор числа от 0 до 1;
  • СЛУЧМЕЖДУ – рандомное значение между указанными числами;
  • ЗНАК – позволяет определить знак указанного значения;
  • ПРОМЕЖУТОЧНЫЕ.ИТОГИ – консолидация значений и подведение итога по списку или базе данных.

Информационные функции

Данные формулы в основном являются средством для анализа данных. Прописать их довольно просто. Их назначение следующее:

  • ЕПУСТО – проверка ячейки на наличие какого-нибудь значения;
  • ЕНД – проверка ячейки на наличие ошибки #Н/Д;
  • ЕЧИСЛО – проверка значения на соответствие числовому формату;
  • ЕОШИБКА – проверка на наличие любой ошибки;
  • ЕТЕКСТ – функция выдает истину, если в аргументе указано текстовое значение;
  • ЕНЕТЕКСТ – аналогичная проверка, только наоборот;
  • ЕОШ – функция вернет истинный результат, если в ячейке будет любая ошибка, отличная от #Н/Д;
  • для проверки четного или нечетного значения используются формулы ЕЧЁТН и ЕНЕЧЁТ;
  • ЕФОРМУЛА – проверка на наличие формулы в указанной ячейке.

Но есть и более сложная функция, о которой стоит поговорить отдельно.

Используя этот инструмент, вы сможете узнать всю нужную информацию об указанной клетке. При этом необходимо использовать обязательный параметр – «Тип сведений», при помощи которого вы сможете узнать:

  • цвет;
  • адрес;
  • столбец;
  • и многое другое.

Более подробно можно узнать на сайте Microsoft.

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

  • И – истина, если все условия истинные;
  • ИЛИ – истина, если хотя бы одно условия истинное;

Для анализа различных условий используются следующие функции:

  • ЕСЛИ – для проверки одного события;
  • УСЛОВИЯ – то же самое, только с огромным количеством условий.

Последняя из указанных выше появилась только в редакторе Excel 2016. Ранее использовался вариант «ЕСЛИМН».

В качестве примера можно привести следующую таблицу.

В данном случае использовались сразу две функции: «ЕСЛИ» и «ИЛИ».

=ЕСЛИ(ИЛИ(D3=»Первая»;D3=»Вторая»);100;0)

Для проверки работы формулы можно использовать конструкцию с «ЕСЛИОШИБКА». Если всё составлено корректно, то вы увидите результат вычислений. В противном случае увидите введенное значение в текстовом виде.

Функции ссылки и поиска

  • ВЫБОР – выбор какого-нибудь значения из списка (массива) данных;
  • СТОЛБЕЦ – вывод номера колонки указанной ячейки;
  • ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ – отображение данных, которые хранятся в отчёте сводной таблицы;
  • ГПР – поиск в массиве данных;
  • ИНДЕКС – выбор какого-нибудь значения согласно дополнительному индексу в указанном диапазоне ячеек;
  • ДВССЫЛ – получение ссылки, которая изначально была задана текстовым значением;
  • ПРОСМОТР – поиск значений в массиве;
  • ПОИСКПОЗ – поиск позиции указанного текста или значения в определенном диапазоне ячеек;
  • СМЕЩ – смещение ссылки относительно указанной ссылки;
  • СТРОКА – возвращает номер строки в указанной ссылке;
  • ТРАНСП – транспонирование массива данных;
  • ВПР – поиск значения в одном массиве и получение данных из ячейки в найденной строке в определенном списке данных.

Функции для работы с базами данных

Формул в данном разделе довольно много. Рассмотрим несколько самых основных и наиболее востребованных. К ним относятся:

  • БИЗВЛЕЧЬ – поиск записи в базе данных, которая соответствует указанному условию выборки;
  • БДСУММ – сумма всех чисел, которые находятся в указанном поле и соответствуют определенным условиям;
  • ДМИН – поиск минимального значения среди всей выборки данных из базы;
  • ДМАКС – поиск максимального значения среди всей выборки данных из базы;

Данный раздел формул очень интересен и полезен. При помощи их можно проводить быстрый анализ какой-нибудь информации либо вытаскивать определенные данные из указанной даты.

  • ДЕНЬ – определяется день в указанной дате;
  • МЕСЯЦ – определяется месяц в указанной дате;
  • ГОД – определяется год в указанной дате;
  • СЕГОДНЯ – вывод текущей даты;
  • НОМНЕДЕЛИ – вывод номера недели на основании указанной даты;
  • ТДАТА – вывод текущей даты и текущего времени;
  • ЧАС – определяется какой час указан в определенной дате;
  • МИНУТЫ – определяется сколько минут указано в определенной дате;
  • СЕКУНДЫ – определяется сколько секунд указано в определенной дате;
  • ДЕНЬНЕД – вычисляется порядковый номер дня недели (отсчет начинается с воскресенья, а не с понедельника).

Кроме этого, есть и более сложные формулы. К ним относятся:

  • РАЗНДАТ – происходит расчет количества лет, месяцев и дней между указанными датами;
  • ДНИ – происходит расчет количества дней между указанными датами (функция появилась в 2013 году);
  • ЧИСТРАБДНИ – происходит расчет количества рабочих дней между указанными датами;
  • ДЕНЬНЕД – преобразование обычной даты в числовом формате в порядковый номер недели;
  • РАБДЕНЬ – вывод даты, которая отстает или опережает указанное количество дней.

Более подробно о последней формуле можно прочитать на официальном сайте Microsoft.

Синтаксис данной функции следующий.

А примеры довольно простые.

Для работы с текстовой информацией в редакторе Excel существует огромный набор различных функций. К самым популярным относятся:

  • СЦЕПИТЬ – в данном случае происходит сцепка различных кусков в один полноценный текст;
  • СОВПАД – проверка двух значений на полное соответствие друг другу;
  • НАЙТИ, НАЙТИБ – поиск фрагмента в другом тексте (функция ищет с учетом регистра букв);
  • ПОИСК, ПОИСКБ – аналогичный поиск, только без учета регистра;
  • ЛЕВСИМВ, ЛЕВБ – копирование первых символов строки (в одном случае расчет происходит посимвольно, а в другом – по байтам);
  • ПРАВСИМВ, ПРАВБ – тот же смысл, только отсчет с правой стороны;
  • ДЛСТР, ДЛИНБ – количество знаков в строчке;
  • ПСТР, ПСТРБ – копирование фрагмента нужного количества символов с указанной позиции для отчета;
  • ЗАМЕНИТЬ, ЗАМЕНИТЬБ – замена определенных знаков в текстовой строке;
  • ПОДСТАВИТЬ – замена одного текста на другой;
  • ТЕКСТ – конвертация числа в текстовый формат;
  • ОБЪЕДИНИТЬ – объединение различных текстовых фрагментов в одно целое (при этом происходит вставка какого-нибудь указателя).

Последняя указанная формула появилась в последней версии Microsoft Excel 2016. Её синтаксис выглядит следующим образом.

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

ПЛТ

Видеоинструкция

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

Введение

1: MicrosoftExcel

1.1 Понятие и возможности MS Excel

1.2 Основные элементы окна MS Excel

1.4 Возможные ошибки при использовании функций в формулах

2: Анализ данных. Использование сценариев

2.1 Анализ данных в MS Excel

2.2 Сценарии

2.3 Пример расчета внутренней скорости оборота инвестиций

Заключение

Список литературы

Введение

Microsoft Office , самое популярное семейство офисных программных продуктов, включает в себя новые версии знакомых приложений, которые поддерживают технологии Internet, и позволяют создавать гибкие интернет-решения

Microsoft Office - семейство программных продуктов Microsoft, которое объединяет самые популярные в мире приложения в единую среду, идеальную для работы с информацией. В Microsoft Office входят текстовый процессор Microsoft Word, электронные таблицы Microsoft Excel, средство подготовки и демонстрации презентаций Microsoft PowerPoint и новое приложение Microsoft Outlook. Все эти приложения составляют Стандартную редакцию Microsoft Office. В Профессиональную редакцию входит также СУБД Microsoft Access.

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

Программа MSExcel применяется при создании комплексных документов в которых необходимо:

· использовать одни и те же данные в разных рабочих листах;

· изменить и восстанавливать связи.

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

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

Более подробно в этой курсовой будет представлено приложение MSExcel, его функции и возможности. А также использование сценариев с их практическим применением.

1. Microsoft Excel

1.1 . Microsoft Excel . Понятия и возможности

Табличный процессор MS Excel (электронные таблицы) – одно из наиболее часто используемых приложений пакета MS Office, мощнейший инструмент в умелых руках, значительно упрощающий рутинную повседневную работу. Основное назначение MS Excel – решение практически любых задач расчетного характера, входные данные которых можно представить в виде таблиц. Применение электронных таблиц упрощает работу с данными и позволяет получать результаты без программирования расчётов. В сочетании же с языком программирования Visual Basic for Application (VBA), табличный процессор MS Excel приобретает универсальный характер и позволяет решить вообще любую задачу, независимо от ее характера.

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

Основные возможности электронных таблиц:

1. проведение однотипных сложных расчётов над большими наборами данных;

2. автоматизация итоговых вычислений;

3. решение задач путём подбора значений параметров;

4. обработка (статистический анализ) результатов экспериментов;

5. проведение поиска оптимальных значений параметров (решение оптимизационных задач);

6. подготовка табличных документов;

7. построение диаграмм (в том числе и сводных) по имеющимся данным;

8. создание и анализ баз данных (списков).

1.2. Основные элементы окна MS Excel

Основными элементами рабочего окна являются:

1. Строка заголовка (в ней указывается имя программы) с кнопками управления окном программы и окном документа (Свернуть, Свернуть в окно или Развернуть во весь экран, Закрыть);

2. Строка основного меню (каждый пункт меню представляет собой набор команд, объединенных общей функциональной направленностью) плюс окно для поиска справочной информации.

3. Панели инструментов (Стандартная, Форматирование и др.).

4. Строка формул, содержащая в качестве элементов поле Имя и кнопку Вставка функции (fx), предназначена для ввода и редактирования значений или формул в ячейках. В поле Имя отображается адрес текущей ячейки.

5. Рабочая область (активный рабочий лист).

6. Полосы прокрутки (вертикальная и горизонтальная).

7. Набор ярлычков (ярлычки листов) для перемещения между рабочими листами.

8. Строка состояния.

1.3 Структура электронных таблиц

Файл, созданный средствами MS Excel, принято называть рабочей книгой. Рабочих книг создать можно столько, сколько позволит наличие свободной памяти на соответствующем устройстве памяти. Открыть рабочих книг можно столько, сколько их создано. Однако активной рабочей книгой может быть только одна текущая (открытая) книга.

Рабочая книга представляет собой набор рабочих листов, каждый из которых имеет табличную структуру. В окне документа отображается только текущий (активный) рабочий лист, с которым и ведётся работа. Каждый рабочий лист имеет название, которое отображается на ярлычке листа в нижней части окна. С помощью ярлычков можно переключаться к другим рабочим листам, входящим в ту же рабочую книгу. Чтобы переименовать рабочий лист, надо дважды щёлкнуть мышкой на его ярлычке и заменить старое имя на новое или путём выполнения следующих команд: меню Формат, строка Лист в списке меню, Переименовать. А можно и, установив указатель мышки на ярлык активного рабочего листа, щёлкнуть правой кнопкой мыши, после чего в появившемся контекстном меню щёлкнуть по строке Переименовать и выполнить переименование. В рабочую книгу можно добавлять (вставлять) новые листы или удалять ненужные. Вставку листа можно осуществить путём выполнения команды меню Вставка, строка Лист в списке пунктов меню. Вставка листа произойдёт перед активным листом. Выполнение вышеизложенных действий можно осуществить и с помощью контекстного меню, которое активизируется нажатием правой кнопки мышки, указатель которой должен быть установлен на ярлычке соответствующего листа. Чтобы поменять местами рабочие листы нужно указатель мышки установить на ярлычок перемещаемого листа, нажать левую кнопку мышки и перетащить ярлычок в нужное место.

Рабочий лист (таблица) состоит из строк и столбцов. Столбцы озаглавлены прописными латинскими буквами и, далее, двухбуквенными комбинациями. Всего рабочий лист содержит 256 столбцов, поименованных от A до IV. Строки последовательно нумеруются числами от 1 до 65536.

На пересечении столбцов и строк образуются ячейки таблицы. Они являются минимальными элементами, предназначенными для хранения данных. Каждая ячейка имеет свой адрес. Адрес ячейки состоит из имени столбца и номера строки, на пересечении которых расположена ячейка, например, A1, B5, DE324. Адреса ячеек используются при записи формул, определяющих взаимосвязь между значениями, расположенными в разных ячейках. В текущий момент времени активной может быть только одна ячейка, которая активизируется щелчком мышки по ней и выделяется рамкой. Эта рамка в Excel играет роль курсора. Операции ввода и редактирования данных всегда производятся только в активной ячейке.

На данные, расположенные в соседних ячейках, образующих прямоугольную область, можно ссылаться в формулах как на единое целое. Группу ячеек, ограниченную прямоугольной областью, называют диапазоном. Наиболее часто используются прямоугольные диапазоны, образующиеся на пересечении группы последовательно идущих строк и группы последовательно идущих столбцов. Диапазон ячеек обозначают, указывая через двоеточие адрес первой ячейки и адрес последней ячейки диапазона, например, B5:F15. Выделение диапазона ячеек можно осуществить протягиванием указателя мышки от одной угловой ячейки до противоположной ячейки по диагонали. Рамка текущей (активной) ячейки при этом расширяется, охватывая весь выбранный диапазон.

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

В целом MS Excel содержит более 400 функций рабочего листа (встроенных функций). Все они в соответствии с предназначением делятся на 11 групп (категорий):

1. финансовые функции;

2. функции даты и времени;

3. арифметические и тригонометрические (математические) функции;

4. статистические функции;

5. функции ссылок и подстановок;

6. функции баз данных (анализа списков);

7. текстовые функции;

8. логические функции;

9. информационные функции (проверки свойств и значений);

10.инженерные функции;

11.внешние функции.

Запись любой функции в ячейку рабочего листа обязательно начинается с символа равно (=). Если функция используется в составе какой-либо другой сложной функции или в формуле (мегаформуле), то символ равно (=) пишется перед этой функцией (формулой). Обращение к любой функции производится указанием её имени и следующего за ним в круглых скобках аргумента (параметра) или списка параметров. Наличие круглых скобок обязательно, именно они служат признаком того, что используемое имя является именем функции. Параметры списка (аргументы функции) разделяются точкой с запятой (;). Их количество не должно превышать 30, а длина формулы, содержащей сколько угодно обращений к функциям, не должна превышать 1024 символов. Все имена при записи (вводе) формулы рекомендуется набирать строчными буквами, тогда правильно введённые имена будут отображены прописными буквами.

1.4 Возможные ошибки при использовании функций в формулах

Добрый день уважаемый пользователь!

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

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

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


  1. . Самой первой функцией стоит изучить функцию СУММ, без нее просто не обходится ни одно математическое действие в таблицах, просуммировать ячейки, или даже множество разбросанных значений в документе, это всё во власти функции СУММ. Также для удобства использования Excel предоставляет вам возможность использования инструмента «автосумма» , что еще более упрощает работу, вам стоит нажать одну кнопочку и весь диапазон чисел будет посчитан в одно мгновение от 2 до миллиона значений, увы, на калькуляторе это будет дольше и не гарантирует правильный результат. Функция таит в себе свои и , которые вам очень пригодятся.

  2. . Второй по важности изучения, стоит функция ЕСЛИ. Эта логическая функция позволит вам производит множество логических вычислений по многим условиям. Функция имеет возможность вложения, а это позволит вам работать с вариантами условий. Может быть, вы и испугаетесь некой сложности функции, но не стоит пугаться ее обманчивости – она очень проста и доступна. Максимально полезна она будет .

  3. . Третьей важной функцией в моем обзоре станет функция СУММЕСЛИ. Эта функция соединяет математические и логические разделы в одном лице и позволит вам собрать и просуммировать значения со всего диапазона по заданному критерию, а это очень поможет, когда строк и столбцов в таблице великое множество. Конечно, есть альтернативы по получению аналогичного результата, но всё же, все остальные варианты будут сложнее. Функция будет очень полезна и бухгалтерам и экономистам.

  4. . Четвёртой по счёту рассмотрим функцию ВПР. Эта функция с раздела , является одной из самых полезных и мощных функций при работе с массивами. Поиск и работа с полученными данными из массива ваших данных будет эффективным при использовании функции ВПР, но у нее есть одно ограничение, она ищет только в вертикальных списках, хотя данные списки используются в 95%, это компенсирует ее недостаток. А если вам нужно горизонтальный поиск, вам поможет . Аналогом этой функции может стать соединение других функций, таких как ПОИСКПОЗ и , но о них отдельно. Очень полезная функция для анализа любых финансовых результатов и построений .

  5. . Пятой функцией нашего топ списка самых полезных функций Excel станет функция СУММЕСЛИМН. Эта функция может все, что умеет третья функция нашего списка, но только немножко больше, а именно суммировать не по одному критерию, а по многим, всё же 127 поддерживаемых критериев это очень сильно. Не стоит забывать, что для корректной работы со многими критериями и диапазонами необходимо пользоваться абсолютными ссылками. Станет полезной многим бухгалтерам и экономистам при работе с большими объемами данных.

  6. . Эта простая функция, которую я предоставил под номером шесть в моем списке ТОП 10, часто спасала меня и помогала получить результат. Достаточно часто мы можем предугадать, что возникнет та или иная ошибка, а если она возникает в средине вычислений, то ломается вся наша вычислительная линейка. Эта функция позволит нам проигнорировать ошибку и подставить вместо нее нужный результат, что позволит сделать намного больше полезных и точных вычислений, особенно актуально применение совместно с логическими функциями. Очень-очень полезная функция, особенно для , так как при их работе частенько приходится работать с ошибками, которые возникают.

  7. . Седьмую ступеньку нашей пирамиды занимает функция ПОИСКПОЗ, которая, как и функция ВПР работает с массивами, ищет и возвращает значения согласно заданным критериям. По большому счёту эта функция часто является альтернативой функции ВПР, особенно когда ее совместить в гармоничный симбиоз с функцией ИНДЕКС. В этом случае вы сможете получить ряд преимуществ, как то поиск с левой стороны, поиск значения более чем 255 символов, а также добавлять и удалять столбики в таблицу поиска, а также многое другое. Пригодится любым специалистам, которые работают с большими объемами информации.

  8. . На восьмом месте я разместил функцию СЧЁТЕСЛИ, которая совмещает математическое начало и логическое, своеобразное соединение функции СЧЁТ и функции ЕСЛИ. Эта функция самое-то в случае, когда вам нужно будет сосчитать что-либо и где-либо, это могут быть и текстовые значения, и , и числовые значения в массивах и многое другое. Несмотря на то, что функция СЧЁТЕСЛИ производит подсчёт только по одному критерию, всё же ее польза большая, да и этого зачастую с головой хватает. Функция в работе достаточно проста и неприхотлива, да и пригодится в работе специалисту любой финансовой специальности.

  9. . Предпоследней из списка функций моего топ списка станет функция СУММПРОИЗВ, не стоить думать, что она имеет также последние значение в работе, как раз наоборот, многие из специалистов считают ее одним из первых в работе хороших экономистов. Она отлично работает с массивами данных, несмотря на простоту ее синтаксиса, ее функциональность огромна, и осуществлять поиск и выборку данных с массивов она делает легко, быстро и чётко. Функция станет незаменима в работе для экономических специальностей.

  10. . Ну, вот добрались и до конца нашего списка самых полезных функций в Excel, который предоставлен, функцией ОКРУГЛ, с раздела функций. Почему именно ее я включил ее, потому что взял во внимание работу бухгалтера, который когда делает расчёт и у него пропадает копейка, это уже личная трагедия и головная боль. Так что, несмотря на ее простоту и непритязательность, ее польза в правильном предоставлении данных станет очень полезной и нужной. Является важной для бухгалтерских вычислений и получения точного результата.

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

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

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

Сбалансировать бюджет — все равно что попасть в рай. Каждый этого хочет, но не желает делать то, что для этого нужно.
Ф. Грэм

Функция – это специальная, заранее созданная, формула для сложных вычислений, в которую пользователю следует ввести лишь необходимые аргументы. Она имеет имя, описывающее ее предназначение (например, СУММ – это сложение нескольких чисел) и аргументы. Последних может быть несколько, но они всегда заключаются в круглые скобки. Далее мы рассмотрим основные функции Эксель и кратко расскажем об их предназначении.

Самые популярные функции

  1. СУММ – используется в Эксель для сложения значений в полях.
  2. ЕСЛИ – позволяет вернуть разные значения в зависимости от соблюдаемых условий.
  3. ПРОСМОТР – необходима для поиска значений, находящихся на идентичной позиции, но в другом столбце или строчке.
  4. ВПР – задействуется для поиска информации во всей таблице Эксель или её отдельных диапазонах. С её помощью легко отыскать номер телефона по фамилии либо, наоборот (по подобию телефонной книги).
  5. ПОИСКПОЗ – применяется для поиска заданного элемента и возвращения его относительной позиции в диапазон.
  6. ВЫБОР – предназначена для выбора значений из списка.
  7. ДАТА – может вернуть определенному периоду его порядковый номер.
  8. ДНИ – позволяет возвратить число дней между заданными датами.
  9. НАЙТИБ, НАЙТИ – обнаруживают вхождение текста в другую текстовую строку.
  10. ИНДЕКС – находит в диапазоне или таблице Эксель значение или ссылку на него и возвращает обратно.

Как вводить функции в таблицу

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

Для начала вам следует выбрать категорию, а после функцию Эксель из алфавитного списка. Программа введет в ячейку знак равенства, название, круглые скобки и откроет следующее диалоговое окно. В него следует внести аргументы (координаты ячеек, в которых располагаются необходимые данные). После нажатия на кнопку «Ок» или Enter на клавиатуре, полученная функция появится в строчке с формулами.

Ребята, мы вкладываем душу в сайт. Cпасибо за то,
что открываете эту красоту. Спасибо за вдохновение и мурашки.
Присоединяйтесь к нам в Facebook и ВКонтакте

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

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

Сводные таблицы

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

Как применять:

  1. Выберите Вставка > .
  2. В диалоговом окне Рекомендуемые сводные таблицы щелкните любой макет сводной таблицы, чтобы увидеть его в режиме предварительного просмотра, а затем выберите тот из них, в котором данные отображаются нужным вам образом. Нажмите кнопку ОК .
  3. Excel добавит сводную таблицу на новый лист и отобразит список полей, с помощью которого можно упорядочить данные в таблице.

Подбор параметра

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

Как применять:

  1. Выберите Данные > Работа с данными > Анализ «что если» > Подбор параметра .
  2. В поле Установить в ячейке введите ссылку на ячейку, в которой находится нужная формула.
  3. В поле Значение введите нужный результат формулы.
  4. В поле Изменяя значение ячейки введите ссылку на ячейку, в которой находится корректируемое значение, и нажмите кнопку ОК .

Условное форматирование

Условное форматирование позволяет быстро выделить на листе важные сведения.

Как применять:

На вкладке Главная в группе Стили щелкните стрелку рядом с кнопкой Условное форматирование и выберите формулу, которая вам понадобится.

Например, если вам нужно выделить все значения меньше 100, выберите Правила выделения ячеек > Меньше , а затем наберите 100. Перед тем как нажать ОК , можно выбрать формат, который будет применяться для подходящих значений.

ИНДЕКС и ПОИСКПОЗ

Если ВПР помогает находить нужные данные только в первом столбце, то, благодаря функциям ИНДЕКС и ПОИСКПОЗ, можно искать информацию внутри таблицы.

Как применять:

  1. Убедитесь, что ячейки с данными образуют сетку, где есть заголовки и названия строк.
  2. Используйте функцию ПОИСКПОЗ : сначала, чтобы найти столбец, в котором расположен искомый элемент, и затем еще раз, чтобы перейти к строке с ответом.
  3. Вставьте ответы в ИНДЕКС , и Excel сможет указать на ячейку, где эти значения пересекаются.