Преобразование формул в значения



Обзор формул

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

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

Ниже приведен пример формулы, умножающей 2 на 3 и прибавляющей к результату 5, чтобы получить 11.

Следующая формула использует функцию ПЛТ для вычисления платежа по ипотеке (1 073,64 долларов США) с 5% ставкой (5% разделить на 12 месяцев равняется ежемесячному проценту) на период в 30 лет (360 месяцев) с займом на сумму 200 000 долларов:

Ниже приведены примеры формул, которые можно использовать на листах.

=A1+A2+A3 Вычисляет сумму значений в ячейках A1, A2 и A3.

=КОРЕНЬ(A1) Использует функцию КОРЕНЬ для возврата значения квадратного корня числа в ячейке A1.

=СЕГОДНЯ() Возвращает текущую дату.

=ПРОПИСН(«привет») Преобразует текст «привет» в «ПРИВЕТ» с помощью функции ПРОПИСН.

=ЕСЛИ(A1>0) Анализирует ячейку A1 и проверяет, превышает ли значение в ней нуль.

Элементы формулы

Формула также может содержать один или несколько из таких элементов: функции, ссылки, операторы и константы.

1. Функции. Функция ПИ() возвращает значение числа Пи: 3,142.

2. Ссылки. A2 возвращает значение ячейки A2.

3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.

4. Операторы. Оператор ^ («крышка») применяется для возведения числа в степень, а оператор * («звездочка») — для умножения.

Использование констант в формулах

Константа представляет собой готовое (не вычисляемое) значение, которое всегда остается неизменным. Например, дата 09.10.2008, число 210 и текст «Прибыль за квартал» являются константами. выражение или его значение константами не являются. Если формула в ячейке содержит константы, но не ссылки на другие ячейки (например, имеет вид =30+70+110), значение в такой ячейке изменяется только после изменения формулы.

Использование операторов в формулах

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

Типы операторов

Приложение Microsoft Excel поддерживает четыре типа операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.

Арифметические операторы

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

Возведение в степень

Операторы сравнения

Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: ИСТИНА либо ЛОЖЬ.

= (знак «больше или равно»)

Больше или равно

Текстовый оператор конкатенации

Амперсанд ( &) используется для объединения (соединения) одной или нескольких текстовых строк в одну.

Соединение или объединение последовательностей знаков в одну последовательность

Выражение «Северный»&«ветер» дает результат «Северный ветер».

Операторы ссылок

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

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

; (точка с запятой)

Оператор объединения. Объединяет несколько ссылок в одну ссылку.

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

Порядок выполнения Excel в Интернете формулах

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

Порядок вычислений

Формулы вычисляют значения в определенном порядке. Формула всегда начинается со знака равно (=).Excel в Интернете интерпретирует знаки после знака равно как формулу. После знака равно вычисляются элементы (операнды), такие как константы или ссылки на ячейки. Они разделены операторами вычислений. Excel в Интернете вычисляет формулу слева направо в соответствии с определенным порядком для каждого оператора в формуле.

Приоритет операторов

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

Возведение в степень

Умножение и деление

Сложение и вычитание

Объединение двух текстовых строк в одну

Использование круглых скобок

Чтобы изменить порядок вычисления формулы, заключите ее часть, которая должна быть выполнена первой, в скобки. Например, следующая формула дает результат 11, так как Excel в Интернете умножение выполняется перед с добавлением. В этой формуле число 2 умножается на 3, а затем к результату прибавляется число 5.

Если же изменить синтаксис с помощью скобок, Excel в Интернете сбавляет 5 и 2, а затем умножает результат на 3, чтобы получить 21.

В следующем примере скобки, в которые заключена первая часть формулы, принудительно Excel в Интернете сначала вычислить ячейки B4+25, а затем разделить результат на сумму значений в ячейках D5, E5 и F5.

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

Функции — это заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления.

Синтаксис функций

Приведенный ниже пример функции ОКРУГЛ, округляющей число в ячейке A10, демонстрирует синтаксис функции.

1. Структура. Структура функции начинается со знака равно (=), за которым следуют имя функции, открывая скобка, аргументы функции, разделенные запятой, и закрывая скобка.

2. Имя функции. Чтобы отобразить список доступных функций, щелкните любую ячейку и нажмите клавиши SHIFT+F3.

3. Аргументы. Существуют различные типы аргументов: числа, текст, логические значения (ИСТИНА и ЛОЖЬ), массивы, значения ошибок (например #Н/Д) или ссылки на ячейки. Используемый аргумент должен возвращать значение, допустимое для данного аргумента. В качестве аргументов также используются константы, формулы и другие функции.

4. Всплывающая подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, всплывающая подсказка появляется после ввода выражения =ОКРУГЛ(. Всплывающие подсказки отображаются только для встроенных функций.

Ввод функций

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

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

Вложенные функции

В некоторых случаях может потребоваться использовать функцию в качестве одного из аргументов другой функции. Например, в приведенной ниже формуле для сравнения результата со значением 50 используется вложенная функция СРЗНАЧ.

1. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

Допустимые типы вычисляемых значений Вложенная функция, используемая в качестве аргумента, должна возвращать соответствующий ему тип данных. Например, если аргумент должен быть логическим, т. е. Если эта функция не работает, Excel в Интернете отобразит #VALUE! В противном случае TE102825393 выдаст ошибку «#ЗНАЧ!».

Предельное количество уровней вложенности функций . В формулах можно использовать до семи уровней вложенных функций. Если функция Б является аргументом функции А, функция Б находится на втором уровне вложенности. Например, в приведенном выше примере функции СРЗНАЧ и СУММ являются функциями второго уровня, поскольку обе они являются аргументами функции ЕСЛИ. Функция, вложенная в качестве аргумента в функцию СРЗНАЧ, будет функцией третьего уровня, и т. д.

Использование ссылок в формулах

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

Стиль ссылок A1

Стиль ссылок по умолчанию По умолчанию в Excel в Интернете используется стиль ссылок A1, который ссылается на столбцы буквами (от A до XFD, всего 16 384 столбца) и ссылается на строки с числами (от 1 до 1 048 576). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, и затем — номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.

Ячейка или диапазон

Ячейка на пересечении столбца A и строки 10

Диапазон ячеек: столбец А, строки 10-20.

Диапазон ячеек: строка 15, столбцы B-E

Все ячейки в строке 5

Все ячейки в строках с 5 по 10

Все ячейки в столбце H

Все ячейки в столбцах с H по J

Диапазон ячеек: столбцы А-E, строки 10-20

Ссылка на другой лист . В приведенном ниже примере функция СРЗНАЧ используется для расчета среднего значения диапазона B1:B10 на листе «Маркетинг» той же книги.

1. Ссылка на лист «Маркетинг».

2. Ссылка на диапазон ячеек с B1 по B10 включительно.

3. Ссылка на лист, отделенная от ссылки на диапазон значений.

Различия между абсолютными, относительными и смешанными ссылками

Относительные ссылки . Относительная ссылка в формуле, например A1, основана на относительной позиции ячейки, содержащей формулу, и ячейки, на которую указывает ссылка. При изменении позиции ячейки, содержащей формулу, изменяется и ссылка. При копировании или заполнении формулы вдоль строк и вдоль столбцов ссылка автоматически корректируется. По умолчанию в новых формулах используются относительные ссылки. Например, при копировании или заполнении относительной ссылки из ячейки B2 в ячейку B3 она автоматически изменяется с =A1 на =A2.

Абсолютные ссылки . Абсолютная ссылка на ячейку в формуле, например $A$1, всегда ссылается на ячейку, расположенную в определенном месте. При изменении позиции ячейки, содержащей формулу, абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам и столбцам абсолютная ссылка не корректируется. По умолчанию в новых формулах используются относительные ссылки, а для использования абсолютных ссылок надо активировать соответствующий параметр. Например, при копировании или заполнении абсолютной ссылки из ячейки B2 в ячейку B3 она остается прежней в обеих ячейках: =$A$1.

Смешанные ссылки Смешанная ссылка имеет абсолютный столбец и относительную строку либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец принимает форму $A 1, $B 1 и так далее. Абсолютная ссылка на строку имеет форму A$1, B$1 и так далее. При изменении позиции ячейки, содержаной формулу, изменяется относительная ссылка, а абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам или вниз по столбцам относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании или заполнении смешанной ссылки из ячейки A2 в B3 она будет меняться с =A$1 на =B$1.

Стиль трехмерных ссылок

Удобный способ для ссылки на несколько листов Трехмерные ссылки используются для анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка содержит ссылку на ячейку или диапазон, перед которой указываются имена листов. Excel в Интернете использует все таблицы, которые хранятся между начальным и конечним именами ссылки. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от листа 2 до листа 13 включительно.

При помощи трехмерных ссылок можно создавать ссылки на ячейки на других листах, определять имена и создавать формулы с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПР, ДИСП.В, ДИСПА и ДИСППА.

Трехмерные ссылки нельзя использовать в формулах массива.

Трехмерные ссылки нельзя использовать вместе с оператор пересечения (один пробел), а также в формулах с неявное пересечение.

Что происходит при перемещении, копировании, вставке или удалении листов . Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для суммирования значений в ячейках с A2 по A5 на листах со второго по шестой.

Вставка или копирование Если вставить листы между листами 2 и 6, Excel в Интернете будет включать в расчет все значения из ячеек с A2 по A5 на добавленных листах.

Удалить Если удалить листы между листами 2 и 6, Excel в Интернете вы вычислите их значения.

Переместить Если переместить листы между листами 2 и 6 в место за пределами диапазона, на который имеется ссылка, Excel в Интернете удалит их значения из вычислений.

Перемещение конечного листа Если переместить лист 2 или 6 в другое место книги, Excel в Интернете скорректирует сумму с учетом изменения диапазона листов.

Удаление конечного листа Если удалить лист 2 или 6, Excel в Интернете скорректирует сумму с учетом изменения диапазона листов между ними.

Стиль ссылок R1C1

Можно использовать такой стиль ссылок, при котором нумеруются и строки, и столбцы. Стиль ссылок R1C1 удобен для вычисления положения столбцов и строк в макросах. В стиле R1C1 Excel в Интернете указывает на расположение ячейки с помощью R, за которым следует номер строки, и C, за которым следует номер столбца.

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

Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее

Абсолютная ссылка на ячейку, расположенную во второй строке второго столбца

Относительная ссылка на строку, расположенную выше текущей ячейки

Абсолютная ссылка на текущую строку

При записи макроса Excel в Интернете некоторые команды с помощью стиля ссылок R1C1. Например, если записать команду (например, нажать кнопку «Автоумма»), чтобы вставить формулу, в которую добавляется диапазон ячеек, Excel в Интернете записи формулы со ссылками с помощью стиля R1C1, а не A1.

Использование имен в формулах

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

Пример использования диапазонов вместо имен

Источник

Все секреты Excel-функции ВПР (VLOOKUP) для поиска данных в таблице и извлечения их в другую

31 октября 2014

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

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

В Telegram-канале « Лайфхакер » только лучшие тексты о технологиях, отношениях, спорте, кино и многом другом. Подписывайтесь!

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

Даже если вы годами используете функцию ВПР, то с высокой долей вероятности эта статья будет вам полезна и не оставит равнодушным. Я, например, будучи IT-специалистом, а потом и руководителем в IT, пользовался VLOOKUP 15 лет, но разобраться со всеми нюансами довелось только сейчас, когда я на профессиональной основе стал обучать людей Excel.

ВПР — это аббревиатура от в ертикального пр осмотра. Аналогично и VLOOKUP — Vertical LOOKUP. Уже само название функции намекает нам, что она производит поиск в строках таблицы (по вертикали — перебирая строки и фиксируя столбец), а не в столбцах (по горизонтали — перебирая столбцы и фиксируя строку). Надо заметить, что у ВПР есть сестра — гадкий утёнок, которая никогда не станет лебедем, — это функция ГПР (HLOOKUP). ГПР, в противоположность ВПР, производит горизонтальный поиск, однако концепция Excel (да и вообще концепция организации данных) подразумевает, что ваши таблицы имеют небольшое количество столбцов и гораздо большее количество строк. Именно поэтому поиск по строкам нам требуется во много раз чаще, чем по столбцам. Если вы в Excel слишком часто пользуетесь функцией ГПР, то, вполне вероятно, что вы чего-то не поняли в этой жизни.

Синтаксис

Функция ВПР имеет четыре параметра:

— искомое значение (редко) или ссылка на ячейку, содержащую искомое значение (подавляющее большинство случаев);

— ссылка на диапазон ячеек (двумерный массив), в ПЕРВОМ (!) столбце которого будет осуществляться поиск значения параметра ;

— номер столбца в диапазоне, из которого будет возвращено значение;

— это очень важный параметр, который отвечает на вопрос, а отсортирован ли по возрастанию первый столбец диапазона . В случае, если массив отсортирован, мы указываем значение ИСТИНА (TRUE) или 1, в противном случае — ЛОЖЬ (FALSE) или 0. В случае, если данный параметр опущен, он по умолчанию становится равным 1.

Держу пари, что многие из тех, кто знает функцию ВПР как облупленную, прочитав описание четвёртого параметра, могут почувствовать себя неуютно, так как они привыкли видеть его в несколько ином виде: обычно там идёт речь о точном соответствии при поиске (ЛОЖЬ или 0) либо же о диапазонном просмотре (ИСТИНА или 1).

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

Источник

Преобразование формул в значения

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

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

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

Способ 1. Классический

Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:

1. Выделите диапазон с формулами, которые нужно заменить на значения.
2. Скопируйте его правой кнопкой мыши – Копировать(Copy) .
3. Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values) :

либо наведитесь мышью на команду Специальная вставка (Paste Special) , чтобы увидеть подменю:

Из него можно выбрать варианты вставки значений с сохранением дизайна или числовых форматов исходных ячеек.

В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special — Values) в открывшемся диалоговом окне:

Способ 2. Только клавишами без мыши

При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:

1. Копируем выделенный диапазон Ctrl + C
2. Тут же вставляем обратно сочетанием Ctrl + V
3. Жмём Ctrl , чтобы вызвать меню вариантов вставки
4. Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант Значения и подтверждаем выбор клавишей Enter :

Способ 3. Только мышью без клавиш или Ловкость Рук

Этот способ требует определенной сноровки, но будет заметно быстрее предыдущего. Делаем следующее:

1. Выделяем диапазон с формулами на листе
2. Хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
3. В появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only) .

После небольшой тренировки делается такое действие очень легко и быстро. Главное, чтобы сосед под локоть не толкал и руки не дрожали 😉

Способ 4. Кнопка для вставки значений на Панели быстрого доступа

Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл — Параметры — Панель быстрого доступа (File — Options — Customize Quick Access Toolbar) . В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель:

Теперь после копирования ячеек с формулами будет достаточно нажать на эту кнопку на панели быстрого доступа:

Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно). Если нажать на клавишу Alt , то Excel подскажет цифру, которая за это отвечает:

Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу

Если вас не пугает слово «макросы», то это будет, пожалуй, самый быстрый способ.

Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так:

Если вам нужно преобразовать в значения текущий лист, то макрос будет таким:

И, наконец, для превращения всех формул в книге на всех листах придется использовать вот такую конструкцию:

Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt + F11 чтобы попасть в Visual Basic, далее Insert — Module). Запускать их потом можно через вкладку Разработчик — Макросы (Developer — Macros) или сочетанием клавиш Alt + F8 . Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить — применяйте их с осторожностью.

Способ 6. Для ленивых

Если ломает делать все вышеперечисленное, то можно поступить еще проще — установить надстройку PLEX, где уже есть готовые макросы для конвертации формул в значения и делать все одним касанием мыши:

• всё будет максимально быстро и просто
• можно откатить ошибочную конвертацию отменой последнего действия или сочетанием Ctrl + Z как обычно
• в отличие от предыдущего способа, этот макрос корректно работает, если на листе есть скрытые строки/столбцы или включены фильтры
• любой из этих команд можно назначить любое удобное вам сочетание клавиш в Диспетчере горячих клавиш PLEX

Источник

Запрос на выборку данных (формулы) в MS EXCEL

13 апреля 2013 г.

Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению стандартного Фильтра ). Произведем отбор значений из исходной таблицы с помощью формул массива . В отличие от применения Фильтра ( CTRL+SHIFT+L или Данные/ Сортировка и фильтр/ Фильтр ) отобранные строки будут помещены в отдельную таблицу.

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

1. Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — число ).

Необходимо отобразить в отдельной таблице только те записи (строки) из Исходной таблицы, у которых цена выше 25.

Решить эту и последующие задачи можно легко с помощью стандартного фильтра . Для этого выделите заголовки Исходной таблицы и нажмите CTRL+SHIFT+L . Через выпадающий список у заголовка Цены выберите Числовые фильтры. , затем задайте необходимые условия фильтрации и нажмите ОК.

Будут отображены записи удовлетворяющие условиям отбора.

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

Критерий (минимальную цену) разместим в ячейке Е6 , таблицу для отфильтрованных данных — в диапазоне D10:E19 .

Теперь выделим диапазон D11:D19 (столбец Товар) и в Строке формул введем формулу массива :

=ИНДЕКС(A11:A19;НАИМЕНЬШИЙ(ЕСЛИ($E$6 ENTER нажмите сочетание клавиш CTRL+SHIFT+ENTER (формула массива будет возвращать несколько значений ).

Те же манипуляции произведем с диапазоном E11:E19 (столбец Цена) куда и введем аналогичную формулу массива :

Чтобы показать динамизм полученного Запроса на выборку, введем в Е6 значение 55. В новую таблицу попадет только 2 записи.

Если в Исходную таблицу добавить новый товар с Ценой 80, то в новую таблицу автоматически будет добавлена новая запись.

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

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

2. Два числовых критерия (Выбрать те Товары, у которых цена попадает в диапазон)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Диапазон Чисел ).

Критерии (нижнюю и верхнюю границы цены) разместим в диапазоне Е5:Е6 .

Т.е. если Цена Товара попадает в указанный интервал, то такая запись появится в новой таблице Отфильтрованные данные.

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

Теперь выделим диапазон D11:D19 и в Строке формул введем формулу массива :

Вместо ENTER нажмите сочетание клавиш CTRL+SHIFT+ENTER .

Те же манипуляции произведем с диапазоном E11:E19 куда и введем аналогичную формулу массива :

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

Чтобы показать динамизм полученного Отчета (Запроса на выборку) введем в Е6 значение 65. В новую таблицу будет добавлена еще одна запись из Исходной таблицы, удовлетворяющая новому критерию.

Если в Исходную таблицу добавить новый товар с Ценой в диапазоне от 25 до 65, то в новую таблицу будет добавлена новая запись.

В файле примера также содержатся формулы массива с обработкой ошибок, когда в столбце Цена содержится значение ошибки, например #ДЕЛ/0! (см. лист Обработка ошибок ).

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

3. Один критерий Дата (Выбрать те Товары, у которых Дата поставки совпадает заданной)

Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий — Дата ).

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

Обратите внимание, что столбец Дат НЕ СОРТИРОВАН.

Введите в ячейку D12 формулу массива:

Примечание : После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER. Это сочетание клавиш используется для ввода формул массива.

Скопируйте формулу массива вниз на нужное количество ячеек. Формула вернет только те значения Товаров, которые были поставлены в диапазоне указанных дат. В остальных ячейках будут содержаться ошибки #ЧИСЛО! Ошибки в файле примера (Лист 4.Диапазон Дат) скрыты с помощью Условного форматирования .

Аналогичную формулу нужно ввести и для дат в столбец E.

В ячейке J12 вычислено количество строк исходной таблицы, удовлетворяющих критериям:

=СЧЁТЕСЛИМН(B12:B20;»>=»&$E$6;B12:B20;» Решение2 : Для отбора строк можно использовать формулы массива, аналогичные Задаче2 (т.е. формулы массива, возвращающие несколько значений ):

Для ввода первой формулы выделите диапазон ячеек G12:G20 . После ввода формулы вместо клавиши ENTER (ВВОД) нужно нажать сочетание клавиш CTRL+SHIFT+ENTER.

Решение3 : Если столбец Дат СОРТИРОВАН, то можно не использовать формулы массива.

Сначала необходимо вычислить первую и последнюю позиции строк, которые удовлетворяют критериям. Затем вывести строки с помощью функции СМЕЩ() .

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

5. Один критерий Дата (Выбрать те Товары, у которых Дата поставки не раньше/ не позже заданной)

Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист Один критерий — Дата (не позже) ).

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

Пользователю требуется найти и вывести в отдельную таблицу события, которые либо уже закончились на заданную дату, либо еще длятся, либо еще не начались.

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

6. Два критерия: Дата и Текст (Выбрать Товары определенного вида, у которых Дата поставки не позже заданной)

Пусть имеется Исходная таблица с перечнем Товаров и Датами поставки (см. файл примера, лист 2 критерия — Дата-Текст ).

В отличие от Задачи 5 будем отбирать строки только того Товара, который указан в критерии. Список дат должен быть отсортирован (для каждого из товаров), по товару сортировка не требуется.

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

= ИНДЕКС(A13:A21; НАИМЕНЬШИЙ(ЕСЛИ(($E$7=$A$13:$A$21)*($E$8>=$B$13:$B$21)*($B$13:$B$21>0);СТРОКА($B$13:$B$21);»»);СТРОКА($B$13:$B$21)-СТРОКА($B$12)) -СТРОКА($B$12))

Условие $E$7=$A$13:$A$21 гарантирует, что будут отобраны товары только определенного типа. Условие $E$8>=$B$13:$B$21 гарантирует, что будут отобраны даты не позже заданной (включая). Условие $B$13:$B$21>0 необходимо, если в диапазоне дат имеются пустые ячейки. Знак * (умножение) используется для задания Условия И (все 3 критерия должны выполняться для строки одновременно).

7. Один Текстовый критерий (Выбрать Товары определенного вида)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — Текст ).

Задача решается аналогично Задачам 1 и 3. Более подробное решение см. в статье Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск .

8. Два Текстовых критерия (Выбрать Товары определенного вида, поставленные в заданный месяц)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (И) ).

Для отбора строк используется формула массива:

Выражение ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) задает оба условия (Товар и Месяц).

Выражение СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК($A$11:$A$19))) формирует массив последовательных чисел <1:2:3:4:5:6:7:8:9>, т.е. номера строк в таблице.

9. Два Текстовых критерия (Выбрать Товары определенных видов)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (ИЛИ) ).

В отличие от Задачи 7 отберем строки с товарами 2-х видов ( Условие ИЛИ ).

Для отбора строк используется формула массива:

= ИНДЕКС(A$11:A$19; НАИБОЛЬШИЙ((($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19))*(СТРОКА($A$11:$A$19)-СТРОКА($A$10)); СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1))

Условие ($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19) гарантирует, что будут отобраны товары только заданных видов из желтых ячеек (Товар2 и Товар3). Знак + (сложение) используется для задания Условие ИЛИ (должен быть выполнен хотя бы 1 критерий).

Вышеуказанное выражение вернет массив <0:0:0:0:1:1:1:0:0>. Умножив его на выражение СТРОКА($A$11:$A$19)-СТРОКА($A$10) , т.е. на массив последовательных чисел <1:2:3:4:5:6:7:8:9>, получим массив позиций (номеров строк таблицы), удовлетворяющих критериям. В нашем случае это будет массив <0:0:0:0:5:6:7:0:0>.

С помощью функции НАИБОЛЬШИЙ() выведем 3 значения из позиции 5 (строка 15 листа), 6 (16) и 7 (17), т.е. значения Товар2, Товар2 и Товар3. Для этого используем выражение СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1 , которое последовательно (начиная со строки 11) будет возвращать числа 3; 2; 1; 0; -1; -2; . Формула НАИБОЛЬШИЙ(. ;3) вернет число 5, НАИБОЛЬШИЙ(. ;2) вернет число 6, НАИБОЛЬШИЙ(. ;1) вернет число 7, а НАИБОЛЬШИЙ(. ;0) и далее вернет ошибку, которую мы скроем условным форматированием .

И наконец, с помощью функции ИНДЕКС() последовательно выведем наши значения из соответствующих позиций: = ИНДЕКС(A$11:A$19;5) вернет Товар2, = ИНДЕКС(A$11:A$19;6) вернет Товар2, = ИНДЕКС(A$11:A$19;7) вернет Товар3.

10. Отбор значений с учетом повторов

В разделе Отбор на основании повторяемости собраны статьи о запросах с группировкой данных. Из повторяющихся данных сначала отбираются уникальные значения, а соответствующие им значения в других столбцах — группируются (складываются, усредняются и пр.).

Наиболее популярные статьи из этого раздела:

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

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

Предположим, что нас интересует сколько и каких партий товаров поставлялось по цене от 1000р. до 2000р. (критерий 1). Причем, партий с одинаковой ценой должно быть минимум 3 (критерий 2).

Решением является формула массива:

Эта формула возвращает номера строк, которые удовлетворяют обоим критериям.

Формула =СУММПРОИЗВ(($C$14:$C$27>=$B$7)*($C$14:$C$27 =$B$10)) подсчитывает количество строк, которые удовлетворяют критериям.

В файле примера на листе «10.Критерий — колич-во повторов» настроено Условное форматирование , которое позволяет визуально определить строки удовлетворяющие критериям, а также скрыть ячейки, в которых формула массива возвращает ошибку #ЧИСЛО!

11. Используем значение критерия (Любой) или (Все)

В фильтре Сводных таблиц MS EXCEL используется значение (Все), чтобы вывести все значения столбца. Другими словами, в выпадающем списке значений критерия содержится особое значение, которое отменяет сам критерий (см. статью Отчеты в MS EXCEL , Отчет №3).

В файле примера на листе «11. Критерий Любой или (Все)» реализован данный вариант критерия.

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

Остальная часть формулы аналогична рассмотренным выше.

12. Актуальная цена

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

Чтобы иметь перечень товаров с актуальными ценами придется использовать формулы:

2) определяем последнюю (максимальную) дату для каждого товара с помощью формулы массива =МАКС((Таблица1[товар]=E8)*Таблица1[дата])

3) наконец, выводим актуальную цену =СУММЕСЛИМН(Таблица1[цена];Таблица1[товар];E8;Таблица1[дата];F8)

Для товара не должно быть повторов дат, иначе цены будут суммироваться (если повторяется последняя дата).

В файле примера приведено решение на листе 12. Актуальная цена.

Источник

ТОП 15 формул в Excel

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

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

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

Стало интересно? Тогда добро пожаловать в обзор самых полезных 15 формул Excel.

Немного терминологии

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

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

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

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

А теперь давайте все это проверим на практике.

Формула 1: ВПР

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

Синтаксис

Это довольно сложная формула, имеющая 4 аргумента, а ее использование имеет множество особенностей.

=ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Давайте более подробно рассмотрим все аргументы:

1. Значение, которое ищется.
2. Таблица. Необходимо, чтобы было искомое значение, находящееся в первой колонке, а также значение, которое возвращается. Последнее располагается где-угодно. Пользователь может самостоятельно определиться, куда вставлять результат формулы.
3. Номер столбца.
4. Интервальный просмотр. Если в этом нет необходимости, то можно не приводить значение этого аргумента. Оно являет собой логическое выражение, обозначающее степень точности совпадения, которое должно быть обнаружено функцией. Если указан параметр «Истина», то тогда Excel будет искать самое близкое значение к указанному в качестве искомого. Если же указан параметр «Ложь», то функция будет искать только те значения, которые находятся в первой колонке.

На этом скриншоте мы пробуем с помощью формулы понять, сколько просмотров было осуществлено по запросу «купить планшет».

Формула 2: Если

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

Синтаксис

В формулу этой функции входит три основных аргумента, и она выглядит так:

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

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

Есть еще один вариант, как можно применять функцию ЕСЛИ – вложенные функции. Здесь может быть намного больше условий, вплоть до 64. Пример рассуждения, соответствующего формуле, приведенной на скриншоте, такой. Если ячейка А2 равна двум, то нужно вывести значение «Да». Если же она имеет другое значение, то необходимо проверить, равна ли ячейка D2 двум. Если да, то нужно выдать значение «нет», если и здесь условие оказывается ложным, то формула должна вернуть значение «возможно».

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

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

Тут синтаксис следующий:

=ЕСЛИ(ЕПУСТО(номер ячейки);«Пустая»;«Не пустая»).

Кроме этого, есть возможность применять вместо функции ЕПУСТО применять стандартную формулу, но указать, что при условии отсутствия никаких значений в ячейке.

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

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

Формула 3: СУММЕСЛИ

Функция СУММЕСЛИ позволяет суммировать данные, при условии их соответствия определенным критериям.

Синтаксис

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

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

Давайте более подробно поймем, что каждый из аргументов собой являет:

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

Так, в данной ситуации Excel суммировал данные о тех запросах, где количество переходов превышает 100000.

Формула 4: СУММЕСЛИМН

Если же условий несколько, то используется родственная функция СУММЕСЛИМН .

Синтаксис

Формула этой функции выглядит так:

=СУММЕСЛИМН(диапазон_суммирования; диапазон_условия1; условие1; [диапазон_условия2; условие2]; …)

Обязательными являются второй и третий аргументы, а именно «Диапазон условия 1» и «условие 1».

Формула 5: СЧЕТЕСЛИ и СЧЕТЕСЛИМН

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

Синтаксис

Чтобы ввести эту функцию, необходимо указать следующую формулу:

=СЧЕТЕСЛИ(диапазон;критерий)

Что означают приведенные аргументы?

1. Диапазон – это совокупность ячеек, среди которой должен осуществляться подсчет.
2. Критерий – условие, учитываемое при выборе ячеек.

Например, этом примере программа посчитала число ключевых запросов, где количество переходов в поисковых системах превышает сто тысяч. В итоге, формула вернула число 3, что означает, наличие трех таких ключевиков.

Если говорить о родственной функции СЧЕТЕСЛИМН, то она, аналогично предыдущему примеру, предоставляет возможность использовать сразу несколько критериев. Ее формула следующая:

=СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)

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

Формула 6: ЕСЛИОШИБКА

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

Синтаксис

У этой функции два аргумента. Синтаксис следующий:

=ЕСЛИОШИБКА(значение;значение_если_ошибка)

1. Значение – непосредственно формула, проверяемая на предмет багов.
2. Значение если ошибка – результат, появляющийся после того, как ошибка будет обнаружена.

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

Формула 7: ЛЕВСИМВ

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

Ее синтаксис следующий:

=ЛЕВСИМВ(текст;[число_знаков])

1. Текст – строка, из какой требуется достать определенный фрагмент.
2. Число знаков – непосредственно количество знаков, которые нужно извлекать.

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

Формула 8: ПСТР

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

Ее синтаксис следующий:

=ПСТР(текст;начальная_позиция;число_знаков).

1. Текст – это строка, в которой содержатся необходимые данные.
2. Начальная позиция – это непосредственно положение того символа, который и служит началом для извлечения текста.
3. Число знаков – количество символов, которые формула должна вытащить из текста.

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

Формула 9: ПРОПИСН

Эта функция все буквы, содержащиеся в определенной строке, делает прописными. Ее синтаксис следующий:

=ПРОПИСН(текст)

Аргумент только один – непосредственно текст, который будет обрабатываться. Можно использовать ссылку на ячейку.

Формула 10: СТРОЧН

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

Синтаксис у нее аналогичный, есть только один аргумент, содержащий текст или адрес ячейки.

Формула 11: ПОИСКПОЗ

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

Шаблон этой формулы такой:

=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;тип_сопоставления)

Первые два аргумента обязательные, последний – нет.

Существует три способа сопоставления:

1. Меньше или равно – 1.
2. Точное – 0.
3. Самое маленькое значение, равно искомому или большего него -1.

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

Формула 12: ДЛСТР

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

Ее синтаксис аналогичен предыдущей:

Так, она может использоваться, чтобы определить длину описания статьи при SEO-продвижении сайта.

Также ее хорошо совмещать с функцией ЕСЛИ .

Формула 13: СЦЕПИТЬ

Эта функция дает возможность сделать несколько строчек из одной. Причем допустимо указание в аргументах как адресов ячеек, так и непосредственно само значение. Формула дает возможность записать до 255 элементов общей длиной не более 8192 символа, чего предостаточно для практики.

Формула 14: ПРОПНАЧ

Эта функция меняет местами прописные и строчные символы.

Синтаксис очень простой:

=ПРОПНАЧ(текст)

Формула 15: ПЕЧСИМВ

Эта формула дает возможность убрать все невидимые знаки (например, перенос строки) из статьи.

Ее синтаксис следующий:

=ПЕЧСИМВ(текст)

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

Выводы

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

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

Источник