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)

Источник: biweb.ru

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Функция индекс в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: support.office.com

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Формулы в Excel

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

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

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

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

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

Макросы

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

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

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

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

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

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

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

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

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

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

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

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

Читайте также:  Exp функция в excel

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

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

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

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

Источник: lumpics.ru

Справочник в EXCEL

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

Создадим Справочник на примере заполнения накладной.

В накладной будем выбирать наименование товара, а цена, единица измерения и НДС, будут подставляться в нужные ячейки автоматически из справочной таблицы Товары , содержащей перечень товаров с указанием, соответственно, цены, единицы измерения, НДС.

Таблица Товары

Эту таблицу создадим на листе Товары с помощью меню Вставка/ Таблицы/ Таблица , т.е. в формате EXCEL 2007 (см. файл примера ). По умолчанию новой таблице EXCEL присвоит стандартное имя Таблица1 . Измените его на имя Товары , например, через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен )

К таблице Товары , как к справочной таблице, предъявляется одно жесткое требование: наличие поля с неповторяющимися значениями. Это поле называется ключевым . В нашем случае, ключевым будет поле, содержащее наименования Товара. Именно по этому полю будут выбираться остальные значения из справочной таблицы для подстановки в накладную.

Для гарантированного обеспечения уникальности наименований товаров используем Проверку данных ( Данные/ Работа с данными/ Проверка данных ):

  • выделим диапазон А2:А9 на листе Товары ;
  • вызовем Проверку данных ;
  • в поле Тип данных выберем Другой и введем формулу, проверяющую вводимое значение на уникальность:

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

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

Для контроля уникальности также можно использовать Условное форматирование (см. статью Выделение повторяющихся значений ).

Теперь, создадим Именованный диапазон Список_Товаров, содержащий все наименования товаров :

  • выделите диапазон А2:А9 ;
  • вызовите меню Формулы/ Определенные имена/ Присвоить имя
  • в поле Имя введите Список_Товаров ;
  • убедитесь, что в поле Диапазон введена формула =Товары[Наименование]
  • нажмите ОК.

Таблица Накладная

К таблице Накладная , также, предъявляется одно жесткое требование: все значения в столбце (поле) Товар должны содержаться в ключевом поле таблицы Товары . Другими словами, в накладную можно вводить только те товары, которые имеются в справочной таблице Товаров , иначе, смысл создания Справочника пропадает. Для формирования Выпадающего (раскрывающегося) списка для ввода названий товаров используем Проверку данных :

  • выделите диапазон C4:C14 ;
  • вызовите Проверку данных ;
  • в поле Тип данных выберите Список;
  • в качестве формулы введите ссылку на ранее созданный Именованный диапазон Список_товаров , т.е. =Список_Товаров .

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

Теперь заполним формулами столбцы накладной Ед.изм., Цена и НДС . Для этого используем функцию ВПР() :

или аналогичную ей формулу

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

В столбцах Цена и НДС введите соответственно формулы: =ЕСЛИОШИБКА(ВПР(C4;Товары;3;ЛОЖЬ);””) =ЕСЛИОШИБКА(ВПР(C4;Товары;4;ЛОЖЬ);””)

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

Источник: excel2.ru

Встроенные функции Excel: краткий справочник по категориям

Функции даты и времени

ДАТА — Возвращает заданную дату в числовом формате Microsoft Excel.

ДАТАЗНАЧ — Преобразует дату из текстового формата в числовой.

ДЕНЬ — Преобразует дату в числовом формате в день месяца.

ДНЕЙ360 — Вычисляет количество дней между двумя датами на основе 360-дневного года.

ДАТАМЕС — Возвращает дату в числовом формате, отстоящую на заданное число месяцев вперед или назад от начальной даты.

КОНМЕСЯЦА — Возвращает дату в числовом формате для последнего дня месяца, отстоящего вперед или назад на заданное число месяцев.

ЧАС — Преобразует дату в числовом формате в часы.

МИНУТЫ — Преобразует дату в числовом формате в минуты.

МЕСЯЦ — Преобразует дату в числовом формате в месяцы.

ЧИСТРАБДНИ — Возвращает количество рабочих дней между двумя датами.

ТДАТА — Возвращает текущую дату и время в числовом формате.

СЕКУНДЫ — Преобразует дату в числовом формате в секунды.

ВРЕМЯ — Возвращает заданное время в числовом формате.

ВРЕМЗНАЧ — Преобразует время из текстового формата в числовой формат.

СЕГОДНЯ — Возвращает числовой формат текущей даты.

ДЕНЬНЕД — Преобразует дату в числовом формате в день недели.

НОМНЕДЕЛИ — Преобразует числовое представление в число, которое указывает, на какую неделю года приходится указанная дата.

РАБ ДЕНЬ — Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней.

ГОД — Преобразует дату в числовом формате в год.

ДОЛЯГОДА — Возвращает долю года, которую составляет количество дней между начальной и конечной датами.

ЯЧЕЙКА — Возвращает информацию о формате, местоположении или содержимом ячейки.

СЧИТАТЬПУСТОТЫ — Подсчитывает количество пустых ячеек в заданном диапазоне.

ТИП.ОШИБКИ — Возвращает номер, соответствующий одному из типов ошибок Microsoft Excel.

ИНФОРМ — Возвращает информацию о текущей операционной среде.

ЕПУСТО — Возвращает логическое значение ИСТИНА, если аргумент является ссылкой на пустую ячейку.

ЕОШ — Возвращает логическое значение ИСТИНА, если аргумент ссылается на любое значение ошибки, кроме #Н/Д.

ЕОШИБКА — Возвращает логическое значение ИСТИНА, если аргумент ссылается на любое значение ошибки.

ЕЧЁТН — Возвращает логическое значение ИСТИНА, если аргумент — четное число.

ЕЛОГИЧ — Возвращает логическое значение ИСТИНА, если аргумент ссылается на логическое значение.

ЕНД — Возвращает логическое значение ИСТИНА, если аргумент ссылается на значение ошибки #Н/Д (значение недоступно).

ЕНЕТЕКСТ — Возвращает логическое значение ИСТИНА, если аргумент ссылается на значение, которое не является текстом.

ЕЧИСЛО — Возвращает логическое значение ИСТИНА, если аргумент ссылается на число.

ЕНЕЧЁТ — Возвращает логическое значение ИСТИНА, если аргумент — нечетное число. ЕССЫЛКА — Возвращает логическое значение ИСТИНА, если аргумент ссылается на ссылку. ЕТЕКСТ — Возвращает логическое значение ИСТИНА, если аргумент ссылается на текст. Ч — Возвращает значение, преобразованное в число. НД — Возвращает значение ошибки #Н/Д. ТИП — Возвращает тип значения.

И — Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА. ЛОЖЬ — Возвращает логическое значение ЛОЖЬ. ЕСЛИ — Выполняет проверку условия.

НЕ — Меняет на противоположное логическое значение своего аргумента.

ИЛИ — Возвращает ИСТИНА, если хотя бы один аргумент имеет значение ИСТИНА.

ИСТИНА — Возвращает логическое значение ИСТИНА.

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

АДРЕС — Возвращает ссылку на отдельную ячейку рабочего листа в виде текста. ОБЛАСТИ — Возвращает количество областей в ссылке. ВЫБОР — Выбирает значение из списка значений по индексу. СТОЛБЕЦ — Возвращает номер столбца, на который указывает ссылка. ЧИСЛСТОЛБ — Возвращает количество столбцов в массиве или ссылке.

ГПР — Ищет значение в первой строке массива и возвращает значение из ячейки в найденном столбце и указанной строке.

ГИПЕРССЫЛКА — Создает ссылку открывающую документ, находящийся на жестком диске, сервере сети или в Интернете.

ИНДЕКС — Использует индекс для выбора значения из ссылки или массива.

ДВССЫЛ — Возвращает ссылку заданную текстовым значением.

ПРОСМОТР — Ищет значения в векторе или массиве.

ПОИСКПОЗ — Ищет значения в ссылке или массиве.

СМЕЩ — Возвращает смещение ссылки относительно заданной ссылки.

СТРОКА — Возвращает номер строки, определяемой ссылкой.

Читайте также:  В excel какая функция

ЧСТРОК — Возвращает количество строк в ссылке.

ДРВ — Извлекает дату в режиме реального времени для программ, поддерживающих автоматизацию СОМ.

ТРАНСП — Возвращает транспонированный массив.

ВПР — Ищет значение в первом столбце массива и возвращает значение из ячейки в найденной строке и указанном столбце.

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

ABS — Возвращает модуль (абсолютную величину) числа.

AC OS — Возвращает арккосинус числа.

ACOSH — Возвращает гиперболический арккосинус числа.

ASIN — Возвращает арксинус числа.

ASINH — Возвращает гиперболический арксинус числа.

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

ATAN2 — Возвращает арктангенс для заданных координат х и у. ATANH — Возвращает гиперболический арктангенс числа.

ОКРВВЕРХ — Округляет число до ближайшего целого или до ближайшего кратного указанному значению.

ЧИСЛКОМБ — Возвращает количество комбинаций для заданного числа объектов.

COS — Возвращает косинус числа.

COSH — Возвращает гиперболический косинус числа.

СЧЁТЕСЛИ — Подсчитывает количество непустых ячеек, удовлетворяющих заданному условию внутри диапазона.

ГРАДУСЫ — Преобразует радианы в градусы.

ЧЁТН — Округляет число до ближайшего четного целого.

ЕХР — Возвращает число е, возведенное в указанную степень.

ФАКТР — Возвращает факториал числа.

ДВФАКТР — Возвращает двойной факториал числа.

ОКРВНИЗ — Округляет число до ближайшего меньшего по модулю целого.

НОД — Возвращает наибольший общий делитель.

ЦЕЛОЕ — Округляет число до ближайшего меньшего целого.

НОК — Возвращает наименьшее общее кратное.

LN — Возвращает натуральный логарифм числа.

LO G — Возвращает логарифм числа по заданному основанию.

LOGIO — Возвращает десятичный логарифм числа.

МОПРЕД — Возвращает определитель матрицы, хранящейся в массиве.

МОБР — Возвращает обратную матрицу (матрица хранится в массиве). М УМНО Ж — Возвращает произведение матриц, хранящихся в массивах. ОСТАТ — Возвращает остаток от деления.

ОКРУГЛТ — Возвращает число, округленное с требуемой точностью. МУЛЬТИНОМ — Возвращает мультиномиальный коэффициент множества чисел. НЕЧЁТ — Округляет число до ближайшего нечетного целого. ПИ — Возвращает число «пи».

СТЕПЕНЬ — Возвращает результат возведения числа в степень.

ПРОНЗВЕД — Возвращает произведение аргументов.

ЧАСТНОЕ — Возвращает целую часть частного при делении.

РАДИАНЫ — Преобразует градусы в радианы.

СЛЧИС — Возвращает случайное число в интервале от 0 до 1.

СЛУЧМЕЖДУ — Возвращает случайное число в заданном интервале.

РИМСКОЕ — Преобразует число в арабской записи к числу в римской как текст.

ОКРУГЛ — Округляет число до указанного количества десятичных разрядов.

ОКРУГЛВНИЗ — Округляет число до ближайшего меньшего по модулю целого.

ОКРУГЛВВЕРХ — Округляет число до ближайшего по модулю большего целого.

РЯД.СУММ — Возвращает сумму степенного ряда, вычисленную по заданной формуле.

ЗНАК — Возвращает знак числа.

SIN — Возвращает синус заданного угла.

SINH — Возвращает гиперболический синус числа.

КОРЕНЬ — Возвращает положительное значение квадратного корня.

КОРЕНЬПИ — Возвращает значение квадратного корня из (число * «пи»),

ПРОМЕЖУТОЧНЫЕ.ИТОГИ — Возвращает промежуточный итог в списке или базе данных.

СУММ — Суммирует аргументы.

СУММЕСЛИ — Суммирует ячейки, удовлетворяющие заданному условию. СУММПРОИЗВ — Возвращает сумму произведений соответствующих элементов массивов. СУММКВ — Возвращает сумму квадратов аргументов.

С.У ММРАЗНКВ — Возвращает сумму разностей квадратов соответствующих значений в двух массивах. СУММСУММКВ — Возвращает сумму сумм квадратов соответствующих элементов двух массивов. СУММКВРАЗН — Возвращает сумму квадратов разностей соответствующих значений в двух массивах.

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

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

ОТБР — Отбрасывает дробную часть числа.

ASC — Преобразует полноширинные (двухбайтные) английские буквы или знаки катакана в текстовой строке в полуширинные (один байт).

БАТТЕКСТ — Преобразует число в текст, используя денежный формат (БАТ).

СИМВОЛ — Возвращает знак с заданным кодом.

ПЕЧСИМВ — Удаляет все непечатаемые знаки из текста.

КОДСИМВ — Возвращает числовой код первого знака в текстовой строке.

СЦЕПИТЬ — Объединяет несколько текстовых элементов в один.

РУБЛЬ — Преобразует число, используя денежный формат.

СОВПАД — Проверяет идентичность двух текстов.

НАЙТИ — Ищет вхождение одного текста в другой (с учетом регистра).

ФИКСИРОВАННЫЙ — Форматирует число и преобразует его в текст с заданным числом десятичных знаков.

JIS — Преобразует полуширинные (однобайтные) английские буквы или знаки катакана в текстовой строке в полноширинные (два байта).

ЛЕВСИМВ — Возвращает самые левые знаки текстового значения. ДЛСТР — Возвращает количество знаков в текстовой строке. СТРОЧН — Делает все буквы в тексте строчными.

ПСТР — Возвращает определенное число знаков из строки текста, начиная с указанной позиции.

PHONETIC — Извлекает фонетические (фуригана) знаки из текстовой строки.

ПРОПНАЧ — Делает прописной первую букву в каждом слове текста.

ЗАМЕНИТЬ — Заменяет знаки в тексте.

ПОВТОР — Повторяет текст заданное число раз.

ПРАВСИМВ — Возвращает самые правые знаки текстовой строки.

ПОИСК — Ищет вхождение одного текста в другой (без учета регистра).

ПОДСТАВИТЬ — Заменяет в текстовой строке старый текст новым.

Т — Преобразует аргумент в текст.

ТЕКСТ — Форматирует число и преобразует его в текст. СЖПРОБЕЛЫ — Удаляет из текста пробелы. ПРОПИСН — Делает все буквы в тексте прописными. ЗНАЧЕН — Преобразует текстовый аргумент в число.

Источник: Леонтьев В. П. Новейшая энциклопедия компьютера 2011. — М.: OЛMA Медиа Групп, 2010. — 960 е.: ил. — (Новейшая энциклопедия.)

Источник: system-repair.net

Функции Excel — простые примеры для начинающего

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

Сразу хотелось бы отметить, что все примеры будем рассматривать в Microsoft office 2010.

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

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

Функция Excel – Сцепить

Данная функция соединяет несколько столбцов в один, например, у Вас фамилия имя отчество расположены в отдельном столбце, а Вам хотелось бы соединить их в один. Также Вы можете использовать эту функцию и для других целей, но надеюсь, смысл ее понятен, пример ниже. Для того чтобы вызвать эту функцию необходимо написать в отдельной ячейке =сцепить(столбец1; столбец2 и т.д.), или на панели нажать кнопку «вставить функцию» и набрать сцепить в поиске, и уже потом в графическом интерфейсе выбрать поля.

Функция Excel – ВПР

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

Вы действуете также как и в предыдущем примере, или пишите или выбираете через графический интерфейс, например:

С описанием полей проблем не должно возникнуть, там все написано. Далее жмете «ОК» и получаете результат:

Функции Excel – Правсимв и Левсимв

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

Функция Excel – Если

Это обычная функция на проверку выражения или значения. Иногда бывает полезна. Например, нам необходимо в столбец C записывать значение «Больше» или «Меньше» на основании сравнения полей A и B т.е. например, если A больше B то записываем «Больше» если меньше то соответственно записываем «Меньше»:

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

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

Источник: info-comp.ru