Конспекти, Корисне

Екстракт розрахунків та програмування Excel

Leave a comment


365 support: Help & learning //support.microsoft.com
Excel functions (alphabetical) .../excel-functions-alphabetical...
Excel VBA reference //learn.microsoft.com

 Список-справочник функций Excel //semtools.guru

Excel functions by category (Excel 2003..2021) //exceljet.net

Список соответствия оригинальных имен функций Excel ru-эквивалентам //ru.excelfunctions.eu

Форматы

Формулы

//

Двойное отрицание -- перед функцией приводит возвращаемое логическое значения {TRUE|FALSE} к соответствующему числовому эквиваленту {1|0}, что удобно во многих расширенных формулах, оперирующих областями ячеек.

//

Значения

Дата и время выражается положительным числом, целая часть которого - это номер суток от даты 1 января 1900 года, а дробная часть - это время деленное на 24 часа суток.

Функции

//

Числовые

MOD (number, divisor)  - озвращает остаток от двух чисел после деления

INT (number) - возвращает целую часть десятичного числа путем округления до целого числа в меньшую сторону

ODD (number) - возвращает следующее нечетное целое число после округления заданного числа в большую сторону. Функция ODD всегда округляет числа в сторону от нуля, поэтому положительные числа становятся больше, а отрицательные — меньше (то есть более отрицательными).

EVEN (number) - округляет числа до следующего четного целого числа. Функция EVEN всегда округляет числа в сторону от нуля, поэтому положительные числа становятся больше, а отрицательные — меньше (то есть более отрицательными).

SUMIF (range, criteria, [sum_range]) - возвращает сумму ячеек, соответствующих одному условию. Критерии могут применяться к датам, числам и тексту. Функция СУММЕСЛИ поддерживает логические операторы (>,<,<>,=) и подстановочные знаки (*,?) для частичного совпадения.

  • range — диапазон, к которому применяются критерии.
    criteria - Критерии для применения.
    sum_range — [необязательный] диапазон для суммирования. Если этот параметр опущен, ячейки в диапазоне суммируются.

Строковые

CHAR ( code ) (СИМВОЛ) возвращает символ, если задан допустимый код символа. CHAR можно использовать для указания символов, которые трудно ввести в формулу.
· Вставка переноса строки выполняется кодом 10. Однако отображается перенос только если для ячейки установлен режим переноса. Выполнить перенос строки формулой идентично ручному вводу невозможно!

CLEAN ( text ) - (ПЕЧСИМВ) удаляет все непечатаемые знаки из текста. Функция используется в том случае, когда текст, импортированный из другого приложения, содержит знаки, печать которых не возможна в данной операционной системе. Например, можно использовать функцию ПЕЧСИМВ, чтобы удалить низкоуровневые компьютерные коды, которые часто встречаются в начале или в конце файлов данных и не могут быть напечатаны. Важно: Функция ПЕЧСИМВ предназначена для удаления из текста первых 32 непечатаемых знаков в 7-разрядном коде ASCII (значения 0—31). В кодировке Юникод имеются дополнительные непечатаемые знаки (со значениями 127, 129, 141, 143, 144 и 157). Сама по себе функция ПЕЧСИМВ не позволяет удалить эти дополнительные непечатаемые знаки.

FIND ( find ; in ; from ) и SEARCH ( find ; in ; from ) выполняет поиск текста find в тексте in
· возвращает позицию от начала текста (нумерация от 1)
· иначе возвращает ошибку #VALUE! ;
· функция FIND отличается чувствительностью к регистру, SEARCH не чувствительна.

LEFT( text ; length ) и RIGHT( text ; length ) возвращает подстроку текста text длиной length от начала и от конца соответственно 

MID ( text ; from ; length ) возвращает подстроку текста text начиная с позиции from длиной length символов
· from если больше длины text, возвращает пустую строку, если меньше 1 вызывает ошибку #VALUE!
· length не может быть опущен, но может превышать длину текста

REPLACE ( old_text, start_num, num_chars, new_text ) в тексте old_text заменяет подстроку начиная с start_num символа и длиной num_chars символов новым текстом new_text

REPLACEB ( old_text, start_num, num_chars, new_text ) аналогична функции REPLACE, но отсчет start_num и num_chars выполняется в байтах

SUBSTITUTE ( text, old_text, new_text, [instance_num] ) заменяет в тексте text образцы old_text на new_text, все или не более instance_num раз

CONCAT(text1, [text2], ..., [text255]) (EXCEL 2016) объединяет строковые значения ячеек
· области не поддерживает
· CONCATENATE() в версиях до Excel 2016

TEXTJOIN ( delimiter, ignore_empty, text1, [text2], …) (Office 2019) последовательно объединяет текст из нескольких значений, ячеек и областей, включая разделитель.

EXACT(text1, text2)  () сравнивает две текстовые строки и при полном совпадении возвращает TRUE, иначе FALSE. Функция чувствительна к регистру, но игнорирует различия в форматировании, что удобно для проверки ввода текста в документ.

VALUE ( text ) (Excel 2007, ЗНАЧЕН) преобразует текстовую строку, представляющую число, в числовое значение.

Области

INDIRECT(ref_text, [a1]) (EXCEL 2003) возвращает действующую ссылку на ячейку или область из текстового представления ссылки
· ref_text текстовое представление ссылки с/без страницы, например: "Sheet1!"&"B1:B"&A2
· a1 логическое значение задает нотация представления, по умолчанию TRUE задает нотацию A1, FALSE задает нотацию R1C1

 

OFFSET( reference, rows, cols, [height], [width] )  (СМЕЩ) повертає посилання на діапазон, віддалений від клітинки або діапазону клітинок на вказану кількість рядків і стовпців. Посилання, що повертається, може бути однією клітинкою або діапазоном клітинок. Кількість рядків і стовпців, які повертаються, можна вказати.

  • reference - посилання на клітинку або діапазон суміжних клітинок
  • rows - обов'язковий аргумент. Кількість рядків (вгору або вниз), на яку потрібно зсунути результат відносно діапазону вихідного посилання, можуть бути додатним числом або від’ємним
  • cols - обов'язковий аргумент. Кількість стовпців (праворуч або ліворуч), на яку потрібно зсунути результат відносно діапазону вихідного посилання, можуть бути додатним числом або від’ємним
  • height – необов'язковий аргумент, має бути додатним числом, висота діапазону посилання, який повертається, у рядках
  • width – необов'язковий аргумент, має бути додатним числом, ширина діапазону посилання, який повертається, у стовпцях

LOOKUP( lookup_value; lookup_vector; [result_vector] ) (Excel 2003) выполняет приблизительный поиск соответствия в диапазоне из одного столбца или одной строки и возвращает соответствующее значение из другого диапазона из одного столбца или одной строки.

  • lookup_value - значение поиска
  • lookup_vector - одномерная область значений для поиска
  • result_vector - одномерная область результатов

VLOOKUP( lookup_value; table_array; col_index; [range_lookup] ) (Excel 2003, ВПР)
и HLOOKUP( lookup_value; table_array; row_index; [range_lookup] ) (Excel 2003, ГПР) використовується для пошуку елементів у таблиці або діапазоні. !!!
функция поиска данных в вертикальной таблице, поддерживает точное совпадение и приблизительное с подстановочными знаками (* ?) для частичного совпадения.

  • lookup_value - значение или шаблон поиска соответствия
  • table_array - область поиска соответствия
  • col_index/row_index - номер колонки/строки содержащей результат
  • range_lookup  - TRUE (по умолчанию) для приблизительного соответствия или FALSE для точного соответствия значению поиска

XLOOKUP( lookup_value; lookup_array; return_array;[if_not_found];[match_mode];[search_mode]) (Excel 2021) шукає діапазон або масив, а потім повертає елемент, який відповідає першому збігу, який він знаходить. Якщо збігів немає, функція XLOOKUP може повернути найближчий (приблизний) збіг

  • lookup_value Обов'язково* Значення для пошуку *Якщо його не вказано, функція XLOOKUP повертає пусті клітинки, знайдені в lookup_array.
  • lookup_array Обов’язковий Масив або діапазон, які потрібно знайти
  • return_array Обов’язковий Масив або діапазон, який потрібно повернути
  • [if_not_found] Необов’язковий Якщо не знайдено припустимий збіг, поверніть введений текст [if_not_found]. Якщо не знайдено припустимий збіг і [if_not_found] відсутній, повертається #N/A .
  • [match_mode] Необов’язковий Укажіть тип збігу:
    • 0 - Точний збіг. Якщо не знайдено, поверніть #N/A. Цей параметр установлено за промовчанням.
    • -1 - Точний збіг. Якщо нічого не знайдено, поверніть наступний менший елемент.
    • 1 - Точний збіг. Якщо не знайдено, поверніть наступний більший елемент.
    • 2 – збіг символу підстановки, де *, ? та ~ мають особливе значення
  • [search_mode] Необов’язковий Укажіть режим пошуку для використання:
    • 1. Виконайте пошук, починаючи з першого елемента. Цей параметр установлено за промовчанням.
    • -1 . Виконайте зворотний пошук, починаючи з останнього елемента.
    • 2. Виконайте двійковий пошук, який залежить від lookup_array сортування за зростанням . Якщо його невідсортовано, буде повернено недійсні результати.
    • 2 – здійснити бінарний пошук, який покладається на те, що масив lookup_array посортовано за спаданням. Якщо його невідсортовано, буде повернено недійсні результати.

SEQUENCE(rows,[columns],[start],[step]) (EXCEL 2021) позволяет генерировать список последовательных чисел в массиве, например 1, 2, 3, 4

Значение

//

FORMULATEXT(reference) возвращает строку формулы в ячейке (включая = в начале) или ошибку

  • ошибка #N/A возникает если
    • ячейка содержит значение без использования формулы
    • длина формулы превышает 8192 символов
    • формула не может быть отображена, например если ячейка защищена
    • ссылка указывает в книгу, которая не открыта

Статистика

//support.microsoft.com/en-us/office/statistical-functions-reference-...

COUNT(value1, [value2], ...) подсчитывает число числовые значений во всех ссылках на ячейки или области.

COUNTA ( value1, [value2], ... )  подсчитывает число непустых ячеек во всех ссылках на ячейки или области.

COUNTIF ( range, pattern ) в заданной области range подсчитывает количество ячеек соответствующих шаблону pattern

  • pattern строка шаблона может включать символ одиночной подстановки ? и множественной *

CORREL(array1,array2) (Excel 2007 КОРРЕЛ) возвращает коэффициент корреляции между двумя множествами данных

PEARSON(array1, array2) (Excel 2007 ПИРСОН) Возвращает коэффициент корреляции Пирсона

RSQ(known_y's,known_x's) (Excel 2007 КВПИРСОН) Возвращает квадрат коэффициента корреляции Пирсона

T.TEST(array1,array2,tails,type) (Excel 2010 СТЬЮДЕНТ.ТЕСТ) возвращает вероятность, связанную с критерием Стьюдента, т.е. вероятно ли, что две выборки получены из одних и тех же двух базовых совокупностей, имеющих одинаковое среднее значение

  • array1 первый набор данных.
  • array2 второй набор данных.
  • tails число хвостов распределения
    • 1 для одностороннего распределения
    • 2 для двустороннего распределения
  • type вид выполняемого t-теста
    • 1 Парная (Paired)
    • 2 Двухвыборочная равная дисперсия (гомоскедастическая)
    • 3 Двухвыборочная неравная дисперсия (гетероскедастическая)

!!!куча

MATCH(lookup_value, lookup_array, [match_type]) !!! ищет указанный элемент в диапазоне ячеек, а затем возвращает относительное положение этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, то формула =ПОИСКПОЗ(25,A1:A3,0) возвращает число 2, поскольку 25 — это второй элемент в диапазоне.

JOINTEXT (?Excel 2019)

Примеры и типовые шаблоны

Получить числовое значение цены из строки "123.45 грн" разделенной неразрывным пробелом:

 

Модули

Меню и панель Developer для работы с макросами Visual Basic может не отображаться, если до этого работа с макросами не выполнялась. Для отображения необходимо открыть меню File / Options / Customize Ribbon / Main Tabs установить флажок  в корне ветки Developer

Бонусы

Интерфейс Excel можно изменить на темную тему, хотя на цвета таблиц это не повлияет. Для этого через меню File>Options в окне Options в разделе General в параметре Office Theme следует выбрать Dark Grey.

//

Источники

 

Leave a Reply