Функции ms excel - IT Новости
Microclimate.su

IT Новости
44 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Функции ms excel

БЛОГ

Только качественные посты

Практический справочник функций Microsoft Excel с примерами их использования

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

Отсюда вытекает вопрос: Сколько нужно знать функций Excel, чтобы решать практически любую задачу в Excel?

Могу с уверенностью, опираясь на свой 17 летний профессиональный опыт работы в Excel, сказать, что достаточно освоить всего около 100 функций…

Представляю Вам ТОП-50 самых главных функций в Microsoft Excel с примерами их использования

– изучив данные Excel функции, у Вас будет достаточно теоретических знаний, чтобы решать практически любую задачу в Excel

( Для перехода к примерам нажмите на название функции. Все примеры — это ссылки на лучшие статьи уважаемых специалистов по Excel и наших партнеров)

1. СУММ / СРЗНАЧ / СЧЁТ / МАКС / МИН (SUM / AVERAGE / COUNT / MAX / MIN) — [Базовые формулы Excel]
2. ВПР (VLOOKUP) — [Ищет значение в первом столбце массива и выдает значение из ячейки в найденной строке и указанном столбце]
3. ИНДЕКС (INDEX) — [По индексу получает значение из ссылки или массива]
4. ПОИСКПОЗ (MATCH) — [Ищет значения в ссылке или массиве]
5. СУММПРОИЗВ (SUMPRODUCT) — [Вычисляет сумму произведений соответствующих элементов массивов (позволяет работать с массивами без формул массива)]
6. АГРЕГАТ / ПРОМЕЖУТОЧНЫЕ.ИТОГИ (AGGREGATE / SUBTOTALS) — [Возвращает общий итог или промежуточный итог в списке или базе данных с учетом фильтров или без учета фильтров]
7. ЕСЛИ (IF) — [Выполняет проверку условия]
8. И / ИЛИ / НЕ (AND / OR / NOT) — [Логические условия, как правило для функции ЕСЛИ]
9. ЕСЛИОШИБКА (IFERROR) — [Если формула возвращает ошибку то что]
10. СУММЕСЛИМН (SUMIFS) — [Суммирует ячейки, удовлетворяющие заданным критериям. Допускается указывать более одного условия]
11. СРЗНАЧЕСЛИМН (AVERAGEIFS) — [Возвращает среднее арифметическое значение всех ячеек, которые соответствуют нескольким условиям]
12. СЧЁТЕСЛИМН (COUNTIFS) — [Подсчитывает количество ячеек, которые соответствуют нескольким условиям]
13. МИНЕСЛИ / МАКСЕСЛИ (MINIFS / MAXIFS) — [Возвращает минимальное/максимальное значение всех ячеек, которые соответствуют нескольким условиям]
14. НАИБОЛЬШИЙ / НАИМЕНЬШИЙ (LARGE / SMALL) — [Возвращает k-ое наибольшее/наименьшее значение в множестве данных]
15. ДВССЫЛ (INDIRECT) — [Определяет ссылку, заданную текстовым значением]
16. ВЫБОР (CHOOSE) — [Выбирает значение из списка значений по индексу]
17. ПРОСМОТР (LOOKUP) — [Ищет значения в массиве]
18. СМЕЩ (OFFSET) — [Определяет смещение ссылки относительно заданной ссылки]
19. СТРОКА / СТОЛБЕЦ (ROW / COLUMN) — [Возвращает номер строки/столбца, на который указывает ссылка]
20. ЧИСЛСТОЛБ / ЧСТРОК (COLUMNS / ROWS) — [Возвращает количество столбцов/строк в ссылке]
21. ОКРУГЛ / ОКРУГЛТ / ОКРУГЛВНИЗ / ОКРУГЛВВЕРХ (ROUND / MROUND / ROUNDDOWN / ROUNDUP) — [Округляет число до указанного количества десятичных разрядов]
22. СЛЧИС / СЛУЧМЕЖДУ / РАНГ (RAND / RANDBETWEEN / RANK) — [Возвращает случайное число]
23. Ч (N) — [Возвращает значение, преобразованное в число]
24. ЧАСТОТА (FREQUENCY) — [Находит распределение частот в виде вертикального массива]
25. СЦЕПИТЬ / СЦЕП / ОБЪЕДИНИТЬ / & (CONCATENATE / CONCAT / TEXTJOIN / &) — [Объединения двух или нескольких текстовых строк в одну]
26. ПСТР (MID) — [Выдает определенное число знаков из строки текста, начиная с указанной позиции]
27. ЛЕВСИМВ / ПРАВСИМВ (LEFT / RIGHT) — [Возвращает заданное количество символов текстовой строки слева / права]
28. ДЛСТР (LEN) — [Определяет количество знаков в текстовой строке]
29. НАЙТИ / ПОИСК (FIND / SEARCH) — [Поиск текста в ячейке с учетом / без учета регистр]
30. ПОДСТАВИТЬ / ЗАМЕНИТЬ (SUBSTITUTE / REPLACE) — [Заменяет в текстовой строке старый текст новым]
31. СТРОЧН / ПРОПИСН / ПРОПНАЧ (LOWER / UPPER) — [Преобразует все буквы текста в строчные/прописные/ или первую букву в каждом слове текста в прописную]
32. ГИПЕРССЫЛКА (HYPERLINK) — [Создает ссылку, открывающую документ, находящийся на жестком диске, сервере сети или в Интернете]
33. СЖПРОБЕЛЫ (TRIM) — [Удаляет из текста все пробелы, за исключением одиночных пробелов между словами]
34. ПЕЧСИМВ (CLEAN) — [Удаляет все непечатаемые знаки из текста]
35. СОВПАД (EXACT) — [Проверяет идентичность двух текстов]
36. СИМВОЛ / ПОВТОР (CHAR / REPT) — [Возвращает знак с заданным кодом/Повторяет текст заданное число раз]
37. СЕГОДНЯ / ТДАТА (TODAY / NOW) — [Возвращает текущую дату в числовом формате / Возвращает текущую дату и время в числовом формате]
38. МЕСЯЦ / ГОД (MONTH / YEAR) — [Вычисляет год / месяц от заданной даты]
39. НОМНЕДЕЛИ (WEEKNUM) — [Преобразует дату в числовом формате в число, которое указывает, на какую неделю года приходится дата]
40. ДАТАЗНАЧ (DATEVALUE) — [Преобразует дату из текстового формата в числовой]
41. РАЗНДАТ (DATEDIF) — [Вычисляет количество дней, месяцев или лет между двумя датами]
42. РАБДЕНЬ (WORKDAY) — [Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней]
43. ЯЧЕЙКА (CELL) — [Возвращает сведения о формате, расположении или содержимом ячейки]
44. ТРАНСП (TRANSPOSE) — [Выдает транспонированный массив]
45. ПРЕОБР (CONVERT) — [Преобразует число из одной системы мер в другую]
46. ПРЕДСКАЗ (FORECAST) — [Вычисляет или предсказывает будущее значение по существующим значениям линейным трендом]
47. ТИП.ОШИБКИ (ERROR.TYPE) — [Возвращает числовой код, соответствующий типу ошибки]
48. ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA) — [Возвращает данные, хранящиеся в сводной таблице]
49. БДСУММ (DSUM) — [Суммирует числа в поле (столбце) записей списка или базы данных, которые удовлетворяют заданным условиям]
50. В качестве бонуса рекомендую изучить Пользовательские форматы в Excel.

После освоения данных функций, следующим этапом рекомендую осваивать инструменты Бизнес- аналитики Business Intelligence (BI)

В Excel к инструментам бизнес-аналитики уровня Self-Service BI относятся бесплатные надстройки «Power»:

  • Power Query — это технология подключения к данным, с помощью которой можно обнаруживать, подключать, объединять и уточнять данные из различных источников для последующего анализа.
  • Power Pivot — это технология моделирования данных, которая позволяет создавать аналитические модели данных, устанавливать отношения и добавлять аналитические вычисления.
  • Power View — это технология визуализации данных, с помощью которой можно создавать интерактивные диаграммы, графики, карты и другие наглядные элементы, позволяющие визуализировать различную информацию.

Ну и если Вы со временем поймете, что возможностей Excel для решения ваших аналитических задач недостаточно, то вам пора переходить к изучению промышленных решений уровня Business Intelligence (BI)

Полезные функции в Microsoft Excel

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

Работа с функциями в Excel

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

Читать еще:  Как скопировать данные в excel

Функция «ВПР»

Одной из самых востребованных функций в Microsoft Excel является «ВПР» («VLOOKUP)». Задействовав ее, можно перетягивать значения одной или нескольких таблиц в другую. При этом поиск производится только в первом столбце таблицы, тем самым при изменении данных в таблице-источнике автоматически формируются данные и в производной таблице, в которой могут выполняться отдельные расчеты. Например, сведения из таблицы, в которой находятся прейскуранты на товары, могут использоваться для расчета показателей в таблице об объеме закупок в денежном выражении.

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

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

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

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

Создается она на вкладке «Вставка» нажатием на кнопку, которая так и называется — «Сводная таблица».

Создание диаграмм

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

Чтобы создать диаграмму, нужно выделить набор ячеек с данными, которые вы хотите визуально отобразить. Затем, находясь на вкладке «Вставка», выбрать на ленте тот тип диаграммы, который считаете наиболее подходящим для достижения поставленных целей.

Более точная настройка диаграмм, включая установку ее наименования и наименования осей, производится в группе вкладок «Работа с диаграммами».

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

Формулы в Excel

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

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

Функция «ЕСЛИ»

Одной из самых популярных функций, которые используются в Excel, является «ЕСЛИ». Она дает возможность задать в ячейке вывод одного результата при выполнении конкретного условия и другого результата в случае его невыполнения. Ее синтаксис выглядит следующим образом: ЕСЛИ(логическое выражение; [результат если истина]; [результат если ложь]) .

Операторами «И», «ИЛИ» и вложенной функцией «ЕСЛИ» задается соответствие нескольким условиям или одному из нескольких условий.

Макросы

С помощью макросов в программе записывается выполнение определенных действий, а потом они воспроизводятся автоматически. Это существенно экономит время на выполнении большого количества однотипной работы. Макросы записываются путем включения захвата своих действий в программе через соответствующую кнопку на ленте.

Запись макросов также можно производить, используя язык разметки Visual Basic в специальном редакторе.

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

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

Форматирование будет выполнено.

«Умная» таблица

Не все пользователи знают, что таблицу, просто начерченную карандашом или границами, Excel воспринимает как простую область ячеек. Заставить программу видеть этот набор данных как таблицу можно через переформатирование. Делается это просто: для начала выделяем нужный диапазон с данными, а затем, находясь на вкладке «Главная», кликаем по кнопке «Форматировать как таблицу». Появится список с различными вариантами стилей оформления, где укажите подходящий.

Таблица также создается нажатием на кнопку «Таблица», которая расположена на вкладке «Вставка», предварительно выделив определенную область листа с данными.

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

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

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

Откроется окно подбора параметра. В поле «Установить в ячейке» вы должны указать ссылку на ячейку, которая содержит нужную формулу. В поле «Значение» должен быть указан конечный результат, который вы хотите получить. В поле «Изменяя значения ячейки» вставьте координаты ячейки с корректируемым значением.

Функция «ИНДЕКС»

Возможности, которые предоставляет функция «ИНДЕКС», в чем-то близки к возможностям функции «ВПР». Она также позволяет искать данные в массиве значений и возвращать их в указанную ячейку. Синтаксис выглядит следующим образом: ИНДЕКС(диапазон_ячеек;номер_строки;номер_столбца) .

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

Отблагодарите автора, поделитесь статьей в социальных сетях.

Функции Excel (по категориям)

Функции упорядочены по категориям в зависимости от функциональной области. Щелкните категорию, чтобы просмотреть относящиеся к ней функции. Вы также можете найти функцию, нажав CTRL+F и введя первые несколько букв ее названия или слово из описания. Чтобы просмотреть более подробные сведения о функции, щелкните ее название в первом столбце.

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

Эта функция используется для суммирования значений в ячейках.

Эта функция возвращает разные значения в зависимости от того, соблюдается ли условие. Вот видео об использовании функции ЕСЛИ.

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

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

Читать еще:  Если текст excel

С помощью этой функции можно найти элемент в диапазоне ячеек, а затем вернуть относительное расположение этого элемента в диапазоне. Например, если диапазон a1: A3 содержит значения 5, 7 и 38, то функция формула = MATCH (7; a1: A3; 0) возвращает число 2, поскольку 7 — второй элемент диапазона.

Эта функция позволяет выбрать одно значение из списка, в котором может быть до 254 значений. Например, если первые семь значений — это дни недели, то функция ВЫБОР возвращает один из дней при использовании числа от 1 до 7 в качестве аргумента «номер_индекса».

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

Функция РАЗНДАТ вычисляет количество дней, месяцев или лет между двумя датами.

Эта функция возвращает число дней между двумя датами.

FIND и НАЙТИБ найдите одну текстовую строку в другой текстовой строке. Они возвращают номер начальной позиции первой текстовой строки из первого символа второй текстовой строки.

Эта функция возвращает значение или ссылку на него из таблицы или диапазона.

Эти функции в Excel 2010 и более поздних версиях были заменены новыми функциями с повышенной точностью и именами, которые лучше отражают их назначение. Их по-прежнему можно использовать для совместимости с более ранними версиями Excel, однако если обратная совместимость не является необходимым условием, рекомендуется перейти на новые разновидности этих функций. Дополнительные сведения о новых функциях см. в статьях Статистические функции (справочник) и Математические и тригонометрические функции (справочник).

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

Возвращает интегральную функцию бета-распределения.

Возвращает обратную интегральную функцию указанного бета-распределения.

Возвращает отдельное значение вероятности биномиального распределения.

Возвращает одностороннюю вероятность распределения хи-квадрат.

Возвращает обратное значение односторонней вероятности распределения хи-квадрат.

Возвращает тест на независимость.

Соединяет несколько текстовых строк в одну строку.

Возвращает доверительный интервал для среднего значения по генеральной совокупности.

Возвращает ковариацию, среднее произведений парных отклонений.

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

Возвращает экспоненциальное распределение.

Возвращает F-распределение вероятности.

Возвращает обратное значение для F-распределения вероятности.

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

Вычисляет, или прогнозирует, будущее значение по существующим значениям.

Возвращает результат F-теста.

Возвращает обратное значение интегрального гамма-распределения.

Возвращает гипергеометрическое распределение.

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

Возвращает интегральное логарифмическое нормальное распределение.

Возвращает значение моды набора данных.

Возвращает отрицательное биномиальное распределение.

Возвращает нормальное интегральное распределение.

Возвращает обратное значение нормального интегрального распределения.

Возвращает стандартное нормальное интегральное распределение.

Возвращает обратное значение стандартного нормального интегрального распределения.

Возвращает k-ю процентиль для значений диапазона.

Возвращает процентную норму значения в наборе данных.

Возвращает распределение Пуассона.

Возвращает квартиль набора данных.

Возвращает ранг числа в списке чисел.

Оценивает стандартное отклонение по выборке.

Вычисляет стандартное отклонение по генеральной совокупности.

Возвращает t-распределение Стьюдента.

Возвращает обратное t-распределение Стьюдента.

Возвращает вероятность, соответствующую проверке по критерию Стьюдента.

Оценивает дисперсию по выборке.

Вычисляет дисперсию по генеральной совокупности.

Возвращает распределение Вейбулла.

Возвращает одностороннее P-значение z-теста.

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

Возвращает элемент или кортеж из куба. Используется для проверки существования элемента или кортежа в кубе.

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

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

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

Возвращает число элементов в множестве.

Возвращает агрегированное значение из куба.

Microsoft Excel/Основные функции

Microsoft Excel
Работа с мастером функцийОсновные функцииДополнительные возможности

Содержание

МИН, МАКС [ править ]

Не забываем всегда перед началом формулы писать «=». Синтаксис:

=МИН(число1; число2; . ; число30) МАКС(число1; число2; . ; число30)

Функции МИН и МАКС принимают от 1 до 30 аргументов (в Office 2007 — до 255) и возвращает минимальный / максимальный из них. Если в качестве аргумента передать диапазон ячеек, из диапазона будет выбрано минимальное / максимальное значение. Эти функции также могут быть вставлены с помощью кнопки «сигма».

СРЗНАЧ [ править ]

СРЗНАЧ(число1; число2; . ; число30)

Функция СРЗНАЧ (среднее значение) принимает от 1 до 30 аргументов (в Office 2007 — до 255) и возвращает их среднее арифметическое (сумма чисел, делённая на количество чисел). Эту функцию также можно вставить с помощью кнопки «сигма»

СТЕПЕНЬ [ править ]

Функция СТЕПЕНЬ возвращает результат возведения первого аргумента («число»), в степень, указанную во втором аргументе («степень»).

СУММ [ править ]

=СУММ(арг1; арг2; . ; арг30)

Функция СУММ принимает от 1 до 30 аргументов (в Office 2007 — до 255) и возвращает их сумму. В качестве аргументов можно передавать адреса диапазонов (что чаще всего и делается), в этом случае просуммируются все числа в диапазоне.

СЧЁТ [ править ]

СЧЁТ(арг1; арг2; . ; арг30)

Функция СЧЁТ принимает от 1 до 30 аргументов (в Office 2007 — до 255) и возвращает количество аргументов, являющиеся числами. Чаще всего функции просто передают адрес диапазона, а она подсчитывает количество ячеек с числами.

СЧЁТПУСТОТЫ [ править ]

В этом синтаксисе оператора left_up_cell: right_down_cell — ячейки (левая верхняя и нижняя правая) с помощью которых задается диапазон в котором считается количество пустых ячеек. Потом количество пустых ячеек выводится в ту ячейку, в которой стоит формула с этим оператором. Принцип работы оператора и подсчета пустых ячеек. Программа встречая оператор СЧЕТПУСТОТЫ в ячейке «смотрит» на диапазон, который указан в круглых скобках. Далее начинает «пробегаться» построчно (по каждой строке диапазона). Найдя пустую ячейку программа прибавляет значение 1 к переменной, которая хранит значение пустых ячеек. При конце диапазона в ячейку выводится значение переменной. Это очень тесно граничит с программированием. Не поняли сейчас — поймете потом. Понять никогда не поздно.

ПИ [ править ]

Возвращает значение тригонометрической константы pi = 3,1415…

ПРОИЗВЕД [ править ]

ПРОИЗВЕД(арг1; арг2; . ; арг30)

Функция ПРОИЗВЕД принимает от 1 до 30 аргументов (в Office 2007 — до 255) и возвращает их произведение. В качестве аргументов можно передавать адреса диапазонов, в этом случае перемножатся все числа в диапазоне.

Читать еще:  Плохо воспроизводит видео онлайн
СЦЕПИТЬ() [ править ]

СЦЕПИТЬ(аргумент1:аргумент2) Этот оператор конкантенирует значения ячеек. Конкантенация — объединение строк. Внимание:

Функции СУММЕСЛИ и СЧЁТЕСЛИ [ править ]

СУММЕСЛИ [ править ]

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

СУММЕСЛИ(диапазон; критерий; [диапазон_суммирования] )

  • диапазон: Проверяемый диапазон, каждая ячейка из которого проверяется на соответствие условию, указанному во втором аргументе.
  • критерий: Условие для суммирования, на соответствие которому проверяется каждая ячейка из проверяемого диапазона. Если необходимо использовать операцию сравнения, то «логическое выражение» указывается без левого операнда и заключается в двойные кавычки (например, «>=100» — суммировать все числа, большие 100). Также можно использовать текстовые значения (например, «яблоки» — суммировать все значения, находящиеся напротив текста «яблоки») и числовые (например, 300 — суммировать значения в ячейках, значения в которых 300).
  • диапазон_суммирования: Необязательный аргумент, используется тогда, когда проверяемый диапазон и диапазон суммирования находятся в разных диапазонах. Если он не указан, то в качестве диапазона суммирования используется проверяемый диапазон (первый аргумент). Если он указан, то суммируются значения из ячеек этого диапазона, находящиеся «напротив» соответствующих ячеек проверяемого диапазона.

СЧЁТЕСЛИ [ править ]

Работает очень похоже на функцию СУММЕСЛИ. В отличие от СУММЕСЛИ, которая суммирует значения из ячеек, СЧЁТЕСЛИ подсчитывает количество ячеек, удовлетворяющих определённому условию. Если написать формулу СУММЕСЛИ(A1:A10; «>10») , будет подсчитана сумма значений из ячеек, значение в которых больше 10. Если же написать СЧЁТЕСЛИ(A1:A10; «>10») , будет подсчитано количество ячеек, значение в которых больше 10.

  • диапазон: Проверяемый диапазон, каждая ячейка из которого проверяется на соответствие условию, указанному во втором аргументе. Из этого же диапазона происходит подсчёт количества ячеек.
  • критерий: Условие, на соответствие которому проверяется каждая ячейка из первого аргумента. Условие записывается аналогично СУММЕСЛИ.

В примере выше фактически подсчитывается количество ячеек, содержащих текст «Яблоки».

Логические функции ЕСЛИ, И, ИЛИ [ править ]

ЕСЛИ [ править ]

ЕСЛИ(логическое_выражение; значение_если_истина; значение_если_ложь).

  • Предназначение: Функция ЕСЛИ выполняет то («Значение если ИСТИНА») или иное («Значение если ЛОЖЬ») действие в зависимости от того, выполняется (равно ИСТИНА) условие или нет (равно ЛОЖЬ).
  • аргумент1. Логическое выражение: Все, что дает в результате логическое значение ЛОЖЬ или ИСТИНА. Обычно либо выражения отношения (A1>=12) либо функции, возвращающие логические значения (И, ИЛИ).
  • аргумент2. Значение если ИСТИНА: любое допустимое в Excel выражение.
  • аргумент3. Значение если ЛОЖЬ: любое допустимое в Excel выражение.
  • возвращаемое значение: может возвращать значения любых типов, в зависимости от аргументов 2 и 3.

Функция ЕСЛИ позволяет организовать в формуле ветвление. Вспомните сказки: налево пойдешь — коня потеряешь, прямо пойдешь — в болото попадешь, направо пойдешь — засосёт в чёрную дыру. Использование функций ЕСЛИ, И, ИЛИ граничит с программированием. Неудивительно, что для многих людей разобраться, как они работают, очень сложно. В голове должен быть чёткий алгоритм решения задачи и требуется хорошее понимание понятия «тип данных»

И [ править ]

Логич_знач И( логич_знач1; логич_знач2; . ; логич_знач30 )

  • Предназначение: Функция И используется тогда, когда нужно проверить, выполняются ли несколько условий ОДНОВРЕМЕННО. Одно из наиболее часто используемых применений функции И — проверка, попадает ли число x в диапазон от x1 до x2.
  • аргументы: Функция И принимает от 1 до 30 аргументов (в Office 2007 — до 256), каждый из которых является логическим значением ЛОЖЬ или ИСТИНА, либо любым выражением или функцией, которое в результате дает ЛОЖЬ или ИСТИНА.
  • возвращаемое значение: Функция И возвращает логическое значение. Если ВСЕ аргументы функции И равны ИСТИНА, возвращает ИСТИНА. Если хотя бы один аргумент имеет значение ЛОЖЬ, возвращает ЛОЖЬ.Примечание: Функция И почти никогда не используется сама по себе, обычно её используют в качестве аргумента других функций, например, ЕСЛИ.
ИЛИ [ править ]

Логич_знач ИЛИ( логич_знач1; логич_знач2; . ; логич_знач30 )

  • Предназначение: Функция ИЛИ используется тогда, когда нужно проверить, выполняется ли ХОТЯ-БЫ ОДНО из многих условий.
  • аргументы: Функция ИЛИ принимает от 1 до 30 аргументов (в Office 2007 — до 256), каждый из которых является логическим значением ЛОЖЬ или ИСТИНА, либо любым выражением или функцией, которое в результате дает ЛОЖЬ или ИСТИНА.
  • возвращаемое значение: Функция ИЛИ возвращает логическое значение. Если ХОТЯ БЫ ОДИН аргумент имеет значение ИСТИНА, возвращает ИСТИНА. Если ВСЕ аргументы имеют значение ЛОЖЬ, возвращает ЛОЖЬ.

Примечание: Функция ИЛИ почти никогда не используется сама по себе, обычно её используют в качестве аргумента других функций, например, ЕСЛИ.

Функция ВПР (Вертикальное Первое Равенство) [ править ]

Для функции необходимо внести следующие данные: какое значение искать (указывается ячейка содержащее значение), массив, в котором происходит поиск, и номер столбца, в котором находится значение, номер повторного значения в массиве, значение которого и будет использовано , которое надо вернуть. Очень удобная Функция значительно расширяет свои возможности при использовании её в паре с логической функцией ЕСЛИ().

Объясняю, как пользоваться функцией: = ВПР (что ищем, таблица где ищем, из какого столбца взять значение, булевская переменная единица или ноль альтернативой или ИСТИНА ЛОЖЬ, параметр 0/ЛОЖЬ ставится, если значения первого столбца не отсортированы). К примеру = ВПР(A1;B:D;2;ЛОЖЬ) В этом примере ищется значение находящееся в ячейке A1 и возвращается значение из столбца C диапазона B:D Эту функцию используют, когда необходимо найти соответствие в определенном столбце для значения, находящегося в первом столбце. Функция как-бы вытаскивает значение из указанного столбца. Допустим, есть таблица вида: ФИО и табельный номер. Функция ВПР() позволяет получить табельный номер по указанной фамилии и инициалам (в вышеприведенном примере фамилия должна находиться в ячейке A1).

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

ГПР () [ править ]

Функция ГПР() очень похожа по принципу действия на функцию ВПР(). Только значение ищется в первой строке таблицы, а результирующее (то значение, которое возвращает ГПР берется из ячейки таблицы с номером столбца в котором встретилось найденное значение и заданном в параметре номера строки. В качестве примера можно привести следующий. Первая строка содержит даты, вторая строка, к примеру, отгрузку по северному направлению, а третья строка — отгрузку по южному направлению. Если эта таблица находится в диапазоне C2:G4, то, для того, чтобы получить отгрузку за дату указанную в ячейке A6 по южному направлению, необходимо воспользоваться формулой =ГПР(A6;C2:G4;3;ЛОЖЬ). При этом, если виды направления (северное и южное) находятся рядом с данными (например, в ячейках B3:B4), то при задании диапазона C2:G4 они не охватываются.

0 0 голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector