Формула массива в Excel что это

Формула массива в Excel

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

На этой странице кратко о том, что такое формула массива (формулы массивов) в Excel.

Информация приводится в сжатой форме. Цель – дать общее представление о том, чем формула массива отличается от обычных формул в Excel.

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

Для того, чтобы ввести формулу массива необходимо нажать одновременно три клавиши Ctrl+Shift+Enter

Визуальным признаком формулы массива (после того, как она введена) являются фигурные скобки { } которыми «обрамляется» формула.

Пример: {=СУММ((E81:E94>2500)*(F81:F94))}

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

Теперь если Вы видите, что в комментарии к формуле сказано, что это — формула массива, скопируйте ее, вставьте на лист Excel, при необходимости отредактируйте и введите формулу одновременным нажатием на Ctrl Shift Enter

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

Как ввести формулу массива

Какие свойства имеет формула массива

Что нужно знать, чтобы написать (создать) формулу массива

Какие бывают виды формул массива

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

В каких случаях применяется формула массива

Какие самые востребованные формулы массива

Какие есть ограничения при использовании формул массива

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

 

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

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

Есть вопросы по обучению Excel удаленно? 

Формула Excel минимум по условию

Формула Excel минимум по условию

Определение минимального или максимального значения в Excel делается очень просто с помощью соответствующих формул-функций: МИН() и МАКС(), однако иногда необходимо узнать минимум или максимум с соблюдением условия.

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

  1. Узнать минимальное число из диапазона, которое больше чем заданное число
  2. Получить максимальное значение, которое меньше, чем нужное нам число

Приведем решение формулой, которая даст нам результат при соблюдении такого условия: «определить минимальное число в диапазоне, которое больше чем 200″.

=МИН(ЕСЛИ(B3:B14>200;B3:B14))

Комментарий: эта формула должна быть введена как формула массива

Чтобы было понятно на конкретном примере, приведем ряд цифр (для проверки формулы поместите эти цифры в диапазон B3:B14)

408
0
-414
290
-482
240
-104
242
440
0
-105
328
274

Наша формула для этого ряда чисел даст результат 240, так как это минимальное число, которое больше чем 200.

Мы рассмотрели одно из решений для задач, когда есть потребность определить не просто максимум или минимум, а МАКС или МИН при определенном условии.

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

— определить минимум, максимум с условием, находящимся в другом столбце. Например, максимум по региону «север»;

минимум/максимум в интервале – то есть задан определенный интервал, к примеру, 200-500 и внутри него нужно понять какое число минимально или же максимально;

второе (третье и т.д.) по счету минимальное/максимальное значение в диапазоне.

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

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

Формулой Excel извлечь текст в скобках

Как формулой Excel вытащить текст в скобках

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

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

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

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

 Формула позволяющая извлечь текст из скобок

=ПСТР(B2;1+ПОИСК(«(«;B2);ПОИСК(«)»;B2)-ПОИСК(«(«;B2)-1)

Комментарии:

— «обрабатываемый» текст должен находиться в определенной ячейке (в примере это яч. B2)

— если в ячейке B2 не будет открывающей и закрывающей скобки, то формула вернет значение ошибки #ЗНАЧ!

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

 

Пример текста (это афоризм, а в скобках указан автор)

Чтобы победить, надо ожидать, что победишь. (Ричард Бах)

и результат (формула извлекла текст из скобок)

Ричард Бах

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

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

 ExcelSkype.Ru – единственный сайт в русскоязычном Internet специализирующийся на дистанционном консультировании и обучении Excel. Помощь или обучение производится через Skype. Все контакты ТУТ

Формула в Excel день недели

Формула в Excel день недели

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

Формула день недели по дате

=ТЕКСТ(B8;»ДДДД»)

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

=ПРОПНАЧ(ТЕКСТ(B8;»ДДДД»))

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

Мы кратко рассмотрели очередную формулу в рамках серии статей, которые мы назвали «1 формула в день».  Если Вас интересует базовый уровень Excel, или Вам необходим «продвинутый», обращайтесь, чтобы получить помощь, обучение или консультацию. Сайт ExcelSkype – удаленное обучение Excel через Skype. 

Как формулой в Excel сложить время

Как формулой в Excel сложить время

Если Вам необходимо к ячейке содержащей время (в формате времени – это важно!) прибавить определенное количество минут, то у Вас может не получиться это сделать, так как сложение времени имеет свои особенности.

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

Есть два принципиальных варианта

В первом из них и время, к которому будем прибавлять, и количество минут, храниться в ячейке в формате времени (очевидно, что такая ячейка может «хранить» не более 59 минут)

Во втором варианте, когда минуты представлены просто обычным числом может быть указано любое количество минут, например, 128 минут (или больше).

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

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

Вот формула в Excel, которая складывает время и минуты

=B2+A2/1440

Предполагается, что в ячейке B2 введено какое-либо время в формате времени (например 14:18), а в ячейке A2 – количество минут, например 128 (не в формате времени, а в т.н. «числовом формате»). Формула выше для нашего примера вернет 16:26

А эта формула считает то же самое, но минуты прописаны внутри

=B2+128/1440

 

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

 Остались вопросы по теме как сложить или вычесть время (минуты, часы, секунды) в Excel? Если «да» – обращайтесь, чтобы получить консультацию или совет.Также, можно обращаться и по другим похожим вопросам: «как прибавить час в Excel?», «как посчитать количество отработанных часов», но и не только…Любые практические вопросы (из практики работы в Excel), которые вызывают затруднения, будут решены и адаптированы именно по Ваши задачи.ExcelSkype.Ru — оперативная (даже экстренная) помощь и обучение Excel не отрываясь от своего рабочего места или домашнего компьютера. Консультации проводятся удаленно через Skype, все контакты ТУТ

Формула как сцепить ячейки в Excel

Как сцепить ячейки в Excel формулой

Ячейки (а точнее их значения) можно сцепить формулой Excel различным образом. Существует специальная функция, которая так и называется СЦЕПИТЬ(), а также есть «знак сцепки».

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

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

В сегодняшней формуле покажем общий принцип, как это делается, используя специальную функцию СЦЕПИТЬ().

Функция имеет относительно простой синтаксис

=СЦЕПИТЬ(текст1;текст2;…)

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

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

=СЦЕПИТЬ(«Общая выручка составила «;B2;» (по итогам месяца)»)

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

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

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

Поэтому знать и понимать, как использовать «склеивание» в Excel крайне необходимо для того, чтобы успешно справляться с различными повседневными проблемами в обработке данных или же создании отчетов (сфера применения СЦЕПИТЬ очень большая)

 Консультации по применению и написанию формул, разъяснение нюансов, обучение Excel. Сайт ExcelSkype.Ru помощь для Вас удаленно через скайп. При возникновении вопросов – обращайтесь чтобы получить консультацию: страница Контакты

Не следует путать понятия «Объединение ячеек» и «Сцепка ячеек»! (объединение это инструмент, который приводит к визуальному изменению отображаемой в ячейках информации и в рамках этой статьи не рассматривается).

Сложение в Excel формула

Как складывать в Excel формулой

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

Любая ячейка в Excel может хранить либо текст, либо число (понятно, что нас интересуют только заполненные ячейки). С числами в excel можно производить все математические действия, однако цель этой заметки – рассмотреть именно сложение.

Есть три основных способа складывать в Excel числа на листе и получать результат в отдельной ячейке.

Первый способ: ввод числовых данных непосредственно в ячейку через «+»

Пример 1

=19+28+384+1,54

Сразу после ввода этой формулы сложения в любую пустую ячейку Excel посчитает ее, и мы увидим сумму (для нашего примера сумма 432,54).

Эта простейшая формула прибавляет внесенные в нее числа, сумма этих чисел будет постоянной и никак не зависит от других чисел на листе или в книге Excel

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

Пример 2

=B3+B5+B8+B17+B23

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

Пример 3

=B4+F4+H4+K4+O4

В формуле примера 3 будут просуммированы ячейки, относящиеся к одной и той же строке.

(смешанные варианты строк и столбцов или ссылок и констант в целях данной статьи не рассматриваются. Вы можете подробнее узнать об этом в статье на нашем сайте «Суммирование в Excel».)

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

Третий способ сложения чисел формулой — с помощью функции СУММ

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

Пример формулы

=СУММ(B1:AA540)

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

 

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

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

 Теперь в рунете есть сайт где можно получить помощь в Excel – сайт ExcelSkype.Ru – профессиональные консультации по Excel, дистанционное обучение по Skype, ответы на возникающие вопросы. Страница контактов, любой удобный Вам способ связи. 

Формула с функцией ЕСЛИ

Формула проверки с использованием функции ЕСЛИ

Одна из наиболее часто используемых функций (категория: «Логические») это функция ЕСЛИ.

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

Для лучшего понимания приведем синтаксис функции ЕСЛИ()

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

Итак, у этой функции три части (они разделены точкой с запятой):

  1. условие (в синтаксисе называется «логическое_выражение»);
  2. то, что должно быть, если условие выполняется (значение, если истина);
  3. то, что посчитает формула, если условие не выполняется (значение, если ложь).

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

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

4,5

4,8

4,2

3,9

3,7

4,7

Необходимо напротив каждого значения (в соседней ячейке — в столбце C) определить хорошист или троечник. Для этих целей как раз подойдет функция ЕСЛИ в сочетании с оператором сравнения (напомним, что операторы сравнения в Excel это знаки «больше», «меньше», «равно» и вариации с ними).

Сформулируем условие, которое будет реализовываться в формуле с использованием функции ЕСЛИ(): если значение ячейки больше 4, то формула должна дать в результате вычислений слово «хорошист», в противном случае – «троечник» (примечание: мы намеренно не рассматриваем варианты «двоечник» или «отличник» так как в нашем ряду чисел нет таких значений).

Итак, формула с функцией ЕСЛИ для решения нашей задачи:

=ЕСЛИ(B2>4;»хорошист»;»троечник»)

Чтобы получить результат по всему ряду данных (средних баллов) введем формулу в ячейку C2 и растянем ее вниз. После этого у нас появится диапазон с однотипными формулами, которые «посчитали» и определили (принято говорить «вернули») как выполняется заданное условие.

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

4,5          хорошист

4,8          хорошист

4,2          хорошист

3,9          троечник

3,7          троечник

4,7          хорошист

Очевидно, что поставленная цель достигнута. Функция ЕСЛИ сделала именно то, что нам было изначально необходимо.

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

Материал приведен здесь лишь в ознакомительных целях и не раскрывает в полной мере тему использования функции ЕСЛИ() в экономических и прочих расчетах.

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

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

 Сайт ExcelSkype – удаленное комплексное обучение Excel, написание, разработка и разъяснение формул Excel. Обращайтесь и Вы обязательно решите свои проблемы и задачи в Excel — страница КОНТАКТЫ 

Как формулой Excel округлить число

Как формулой Excel округлить число

В первых двух заметках из серии «1 формула в день» мы привели формулы, в которых использовали функции из категорий «Дата и время» и «Текстовые» (о категориях функций – см. внизу страницы)

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

 Формула, позволяющая округлять числовое значение до нужного числа разрядов

=ОКРУГЛ(B2;1)

Формула работает очень просто. Она округляет число в указанной ячейке (в нашем случае – это ячейка B2) до 1 знака после запятой. То есть, если ячейка B2 содержит число 12,43977, то в результате работы формулы мы получим число 12,4

Синтаксис этой формулы: ОКРУГЛ(число;число_разрядов)

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

Округление чисел – одна из востребованных операций в Excel. Кроме функции ОКРУГЛ в Excel есть еще 7 функций, которые предназначены для округления чисел (различные варианты округления вверх, округления вниз, а также округление до целого кратного заданному числу).

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

Внешний вид окна «Мастер функций» в Excel

Формулы в Excel обычно включают в себя одну или несколько функций, а функции в Excel делятся на такие категории: «Финансовые», «Дата и время», «Математические», «Статистические», «Ссылки и массивы», «Работа с базой данных», «Текстовые», «Логические», «Проверка свойств и значений», «Инженерные», «Аналитические», «Совместимость». На скриншоте показано «Окно мастера функций Excel», там можно увидеть перечень категорий функций.

Буквы прописными формулой в Excel

Как сделать все буквы прописными формулой в Excel

Эта вторая по счету заметка из серии «Формула в день».

В ней будет рассмотрена формула, с помощью которой можно сделать все буквы прописными.

Для тех, кто не знает, что такое прописные буквы…

Прописные буквы — это то же самое, что большие буквы (наиболее часто все большие буквы идут в аббревиатурах). Пример слова-аббревиатуры написанного с прописных букв: SMS (по русски СМС).

Собственно, формула, делающая все буквы текста ячейки «большими»:

=ПРОПИСН(B2)

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

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

 Сайт ExcelSkype – помощь в написании формул, консультации, обучение Excel по Skype. Обращайтесь чтобы получить помощь или взять пару-тройку уроков как по excel в целом, так и по формулам в частности. Страница КОНТАКТЫ – любой удобный Вам способ связи.