Excel find function

Поиск среднего арифметического

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

Способ 1: Рассчитать среднее значение через «Мастер функций»

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

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

Способ 2: Автоматический подсчет результата в выделенных ячейках

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

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

Способ 3: Использование панели формул

Еще один способ перейти в окно функции:

Самый быстрый способ, при котором не нужно долго искать в меню необходимы пункты.

Способ 4: Ручной ввод

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

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

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

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

Самый простой и известный способ найти среднее арифметическое набора чисел — это воспользоваться специальной кнопкой на ленте Microsoft Excel. Выделяем диапазон чисел, расположенных в столбце или в строке документа. Находясь во вкладке «Главная», жмем на кнопку «Автосумма», которая расположена на ленте в блоке инструментов «Редактирование». Из выпадающее списка выбираем пункт «Среднее».

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

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

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

Суммирование ячеек с числами и текстом в Excel

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

Вид таблицы данных:

Для расчета суммарной выручки за 1-ый денек используем последующую формулу:

1-ая функция ЕСЛИ инспектирует условие, данное функцией И.
Если оба проверяемых выражения (результаты работы рассматриваемой функции) возвратят значение ИСТИНА (другими словами являются числами), будет выполнена функция СУММ.
Если одна из функций ЕЧИСЛО возвратит значение ЛОЖЬ, будет выполнена проверка доборной функцией ЕСЛИ (проверка первой ячейки на содержание числовых данных).
Если 1-ая ячейка хранит число, будет возвращено его значение, по другому – ворачивается значение, хранящееся в иной ячейке.

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

Функции в Excel: для чего они используются?

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

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

Как их вводить?

Как и функции, формулы следует начинать со знака «=» в строке ввода. Затем следует аббревиатура, обозначающая выражение, после которой ставятся скобки (как на примере выше). Внутри размещены аргументы, являющиеся графическим отображением значений, которые используются при расчетах. В таком качестве функции в Excel могут использовать не только отдельные ячейки, но и отдельное значение, а также целые серии ссылок на ячейки. Так, выражение «=СУММ (12;24)» выдаст вам результат «36», тогда как похожая система «=СУММ (А12:А24)» выдаст сумму тех чисел, которые находятся в указанном диапазоне.

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

Как мы уже и говорили, этот инструмент является настолько мощным, что позволяет решать даже чрезвычайно сложные уравнения. Для этого Excel (значение функций которого может быть самым разным) использует в сложных системах другие выражения. Чтобы было понятнее, приведем практический пример: «=СУММ (А5:А10;СРЗНАЧ(В7:С10))». Отметим, что поддерживается семикратная вложенность. Конечно, вы можете ввести и больше аргументов, но считаться такое выражение уже не будет.

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

«Мастер»

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

отыщите вкладку «Вставка» и выберите в ней подпункт «Функция»;
можно сократить время, отыскав на панели инструментов кнопку «Вставка функции».

Как мы уже и говорили, применять табличный редактор от Microsoft можно также студентам, так как он дает возможность просчитывать и более «насущные» примеры. Так, график функции в Excel позволит получить удобное и наглядное графическое решение задач вида «y = f(x), отрезок . Шаг h=0,1». Это сильно облегчит учебный процесс.

Ищем оптимальное решение задачи с неизвестными параметрами в Excel

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

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

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

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

Константы — исходная информация. К ней относится удельная маржинальная прибыль, стоимость каждой перевозки, нормы расхода товарно-материальных ценностей. В нашем случае — производительность работников, их оплата и норма в 1000 изделий. Также константа отражает ограничения и условия математической модели: например, только неотрицательные или целые значения. Мы вносим константы в таблицу цифрами или с помощью элементарных формул (СУММ, СРЗНАЧ).

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

При заполнении функции «Поиск решений» важно оставить ячейки пустыми — программа сама найдет значения

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

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

Теперь перейдем к самой функции.

1) Чтобы включить «Поиск решений», выполните следующие шаги:

нажмите «Параметры Excel», а затем выберите категорию «Надстройки»;
в поле «Управление» выберите значение «Надстройки Excel» и нажмите кнопку «Перейти»;
в поле «Доступные надстройки» установите флажок рядом с пунктом «Поиск решения» и нажмите кнопку ОК.

2) Теперь упорядочим данные в виде таблицы, отражающей связи между ячейками. Советуем использовать цветовые обозначения: на примере красным выделена целевая функция, бежевым — ограничения, а желтым – изменяемые ячейки.

Не забудьте ввести формулы. Стоимость заказа рассчитывается как «Оплата труда за 1 изделие» умножить на «Число заготовок, передаваемых в работу». Для того, чтобы узнать «Время на выполнение заказа», нужно «Число заготовок, передаваемых в работу» разделить на «Производительность».

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

4) Заполните параметры «Поиска решений» и нажмите «Найти решение».

Совокупная стоимость 1000 изделий рассчитывается как сумма стоимостей количества изделий от каждого работника. Данная ячейка (Е13) — это целевая функция. D9:D12 — изменяемые ячейки. «Поиск решений» определяет их оптимальные значения, чтобы целевая функция достигла минимума при заданных ограничениях.

В нашем примере следующие ограничения:

общее количество изделий 1000 штук ($D$13 = $D$3);
число заготовок, передаваемых в работу — целое и больше нуля либо равно нулю ($D$9:$D$12 = целое, $D$9:$D$12 > = 0);
количество дней меньше либо равно 30 ($F$9:$F$12 > окажут вам помощь. Это отличный шанс вместе экспертом проработать проблемные вопросы и составить карьерный план.

Зачем нужна функция ВПР и когда её используют

Представьте, что вы продаёте автомобили. У вас есть каталог с характеристиками авто и их стоимостью. Также у вас есть таблица с данными клиентов, которые забронировали эти автомобили.

Это каталог автомобилей с ценами

Это список клиентов: указаны забронированные авто, но нет цен

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

Просто скопировать и вставить эту колонку не получится. Искать каждое авто вручную и переносить цены — долго.

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

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

Excel SEARCH функция

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

Синтаксис функции ПОИСК в Excel:

=SEARCH (find_text, within_text, )

Аргументы:

find_text: Необходимые. Символ или текст, который вы хотите найти.
within_text: Необходимые. Текстовая строка, в которой нужно искать.
start_num: Необязательный. С какой позиции вы хотите начать поиск в текстовой строке. Если этот аргумент опущен, поиск начинается с первого символа данной текстовой строки.

Заметки:

1. Функция ПОИСК вернет позицию первого появления найти_текст внутри текстовой строки, если start_num опущен
2. Функция ПОИСК не чувствительна к регистру, если вам нужно выполнить поиск с учетом регистра, вы должны использовать функцию НАЙТИ.
3. Если значение найти_текст не найден, функция ПОИСК вернет # ЗНАЧ! значение ошибки.
4. Если start_num не больше 0 или больше длины внутри_текст, Значение! также возвращается значение ошибки.
5. Функция ПОИСК позволяет использовать подстановочные знаки в найти_текст Аргумент:
- Вопросительный знак (?): соответствует одному символу;
- Звездочка (*): соответствует любой серии символов;
- Чтобы найти настоящий вопросительный знак (?) Или звездочку (*), введите перед ними тильду (~), например: ~* или ~?.

Примеры:

Пример 1: Поиск символа или текста в текстовой строке (с начала)

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

=SEARCH(B2, A2)

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

Затем вы получите позицию искомого текста от начала текстовой строки, см. Снимок экрана:

Пример 2: поиск символа или текста в текстовой строке (с указанным началом)

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

Введите или скопируйте следующую формулу в пустую ячейку:

=SEARCH(B2, A2,C2)

Внимание: В приведенной выше формуле B2 содержит ли ячейка символ или текст, положение которого вы хотите найти, A2 текстовая строка, в которой вы хотите выполнить поиск, и C2 — это номер, с которого вы хотите начать поиск в текстовой строке. Затем были возвращены позиции символа или текста в текстовой строке, с которой вы хотите начать поиск, см

Снимок экрана:

Затем были возвращены позиции символа или текста в текстовой строке, с которой вы хотите начать поиск, см. Снимок экрана:

Пример 3: Найдите и извлеките текст между скобками

Если у вас есть список текстовых строк, некоторые тексты которых заключены в круглые скобки, теперь вы просто хотите извлечь текст между круглыми скобками, здесь функция ПОИСК, объединяющая функцию MID, может помочь вам решить эту задачу.

Вставьте следующую формулу в пустую ячейку:

=MID(A2,SEARCH(«(«,A2)+1, SEARCH(«)»,A2)-SEARCH(«(«,A2)-1)

И все тексты между скобками были извлечены сразу, см. Снимок экрана:

Дополнительные функции:

Функция Excel ВПРАВО
Функция RIGHT используется для возврата текста справа от текстовой строки.

Функция ЧИСЛО в Excel
Функция NUMBERVALUE помогает вернуть действительное число из числа, хранящегося в виде текста.

Функция ЗАМЕНИТЬ в Excel
Функция ЗАМЕНИТЬ может помочь вам найти и заменить символы на основе заданного местоположения в текстовой строке новым текстом.

Внедрение функции

В файле примера приведены несколько вариантов проверок:

1. Если в качестве значения на вход подается число и формат ячейки НЕ был установлен Текстовый, то функция возвратит логическое значение ИСТИНА. Если формат ячейки с числом поменять на Текстовый уже опосля того, как функция возвратила значение ИСТИНА, то она не станет возвращать значение ЛОЖЬ.

2. Если в качестве значения на вход подается число и формат ячейки был установлен Текстовый (до ввода числа в ячейку), то функция возвратит логическое значение ЛОЖЬ. Если формат ячейки с числом поменять на Числовой уже опосля того, как функция возвратила значение ЛОЖЬ, то она не станет возвращать значение ИСТИНА.

Вывод: функция ЕЧИСЛО() не пробует преобразовать значения в числовой формат. Но, ее просто вынудить созодать это. Если в ячейке А1 число сохранено как текст, то формула = ЕЧИСЛО(–A1) возвратит ИСТИНА, а = ЕЧИСЛО(A1) – ЛОЖЬ.

3. Логические значения ЛОЖЬ и ИСТИНА формально в EXCEL числами не являются и это обосновывает тот факт, что формулы =ЕЧИСЛО(ЛОЖЬ) и =ЕЧИСЛО(ИСТИНА) возвратят ЛОЖЬ. Но, значениям ЛОЖЬ и ИСТИНА сопоставлены значения 0 и 1 соответственно, потому формулы =ЕЧИСЛО(–ЛОЖЬ) и =ЕЧИСЛО(–ИСТИНА) возвратят ИСТИНА.

4. Функция ЕЧИСЛО() обычно употребляется в паре с функцией ЕСЛИ() . К примеру, формула =ЕСЛИ(ЕЧИСЛО(B6);»Число»;»Не число») возвратит слово Число, если в ячейке В6 находится число (в числовом формате).

«Информационные технологии. Работа с электронными таблицами Excel» от Российского экономического университета им. Г.В.Плеханова

Язык: русский.

Длительность: 5 часов видео, 25 видеоуроков + 113 тестов.

Формат обучения: видеоуроки + тесты.

Уровень сложности: средний.

Обратная связь: нет.

Программа обучения:

Цели и задачи курса.
Пользовательский интерфейс Excel.
Рабочая книга, рабочий лист. Операции с рабочими листами.
Ввод и типы данных.
Редактирование данных.
Форматирование и печать электронной таблицы.
Выполнение расчетов в электронных таблицах.
Создание формул. Адресация.
Категории функций.
Вложенные функции.
Формулы массивов.
Аудит формул в Excel.
Графическая интерпретация данных.
Элементы визуализации.
Диаграммы.
Спарклайны.
Условное форматирование.
3D-карта.
Анализ данных.
Сортировка.
Промежуточные итоги.
Фильтры.
Консолидация данных.
Сводные таблицы и диаграммы.
Анализ «что если». Поиск решения.
Подбор параметра.
Таблица подстановки данных.
Сценарии.
Решение задач оптимизации.

Особенности курса:

Курс создан в РЭУ им. Г.В.Плеханова.
Целевая аудитория — студенты экономических вузов.
Для прохождения курса необходимо обладать знаниями по математике и информатике на уровне школьной программы.

О преподавателях:

Марина Романовна Меламуд — кандидат педагогических наук, доцент, заместитель декана Факультета дистанционного обучения Российского экономического университета им. Г.В. Плеханова
Вера Григорьевна Герасимова — доцент кафедры Информатики Российского Экономического Университета им. Г.В. Плеханова
Павел Евгеньевич Коваль — доцент кафедры Информатики Российского Экономического Университета им. Г.В. Плеханова
Наталья Женова — старший преподаватель кафедры информатики Российского Экономического Университета им. Г. В. Плеханова.

Function 18: SEARCH

Function SEARCH (SEARCH) searches for a text string within another text string, and if found, reports its position.

How can I use the SEARCH function?

Function SEARCH (SEARCH) searches for a text string within another text string. She can:

Find a string of text inside another text string (case insensitive).
Use wildcard characters in your search.
Determine the starting position in the viewed text.

SEARCH Syntax

Function SEARCH (SEARCH) has the following syntax:

find_text (search_text) is the text you are looking for.
within_text (text_for_search) – a text string within which the search is performed.
start_num (start_position) – if not specified, the search will start from the first character.

Traps SEARCH (SEARCH)

Function SEARCH (SEARCH) will return the position of the first matching string, case insensitive. If you need a case sensitive search, you can use the function FIND (FIND), which we will meet later in the marathon 30 Excel functions in 30 days.

Поиск ячеек

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

Для поиска ячеек с формулами выполняются следующие действия.

В открытом документе выделить ячейку или диапазон ячеек (в первом случае поиск идет по всему листу, во втором – в выделенных ячейках).
Во вкладке «Главная» выбрать функцию «Найти и выделить».
Обозначить команду «Перейти».
Выделить клавишу «Выделить».
Выбрать «Формулы».
Обратить внимание на список пунктов под «Формулами» (возможно, понадобится снятие флажков с некоторых параметров).
Нажать клавишу «Ок».

Для поиска объединенных ячеек потребуется выполнение следующих манипуляций.

Перейти во вкладку «Главная».
Выбрать функцию «Найти и выделить».
Нажать на команду «Найти».
Перейти в «Параметры» и выбрать «Формат».
Здесь выделить функцию «Выравнивание», поставить отметку «Объединить ячейки».
Нажать на «Ок».
Нажать на кнопку «Найти все» и проанализировать список ячеек, которые объединены на соответствующем листе.

При нажимании кнопкой мыши на элемент в списке происходит выделение объединенной ячейки на листе. Дополнительно доступна функция «Отменить объединение ячеек».

Выполнение представленных выше действий приводит к нахождению всех объединенных ячеек на листе и при необходимости отмене данного свойства. Для поиска скрытых ячеек проводятся следующие действия.

Выбрать лист, требующий анализа на присутствие скрытых ячеек и их нахождения.
Нажать клавиши «F5_гт_ Special».
Нажать сочетание клавиш «CTRL + G_гт_ Special».

Можно воспользоваться еще одним способом для поиска скрытых ячеек:

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

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

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

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

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

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

выбрать ячейку, предусматривающую условное форматирование, требующую поиска;
выбрать группу «Редактирование» во вкладке «Главная»;
нажать на кнопку «Найти и выделить»;
выбрать категорию «Выделить группу ячеек»;
установить свойство «Условные форматы»;
напоследок нужно зайти в группу «Проверка данных» и установить аналогичный пункт.

Примеры использования функции НАЙТИ в Excel

Пример 1. Ищем слово в текстовой строке (с начала строки)

На примере выше мы ищем слово “Доброе” в словосочетании “Доброе Утро”. По результатам поиска, функция выдает число “1”, которое обозначает, что слово “Доброе” начинается с первой по очереди буквы в, заданной в качестве области поиска, текстовой строке.

Обратите внимание, что так как функция НАЙТИ в Excel чувствительна к регистру, вы не сможете найти слово “доброе” в словосочетании “Доброе утро”, так как оно написано с маленькой буквы. Для того, чтобы осуществить поиска без учета регистра следует пользоваться функцией SEARCH

Пример 2. Ищем слово в текстовой строке (с заданным порядковым номером старта поиска)

Третий аргумент функции НАЙТИ указывает позицию, с которой функция начинает поиск искомого значения. На примере выше функция возвращает число “1” когда мы начинаем поиск слова “Доброе” в словосочетании “Доброе утро” с начала текстовой строки. Но если мы зададим аргумент функции start_num (нач_позиция) со значением “2”, то функция выдаст ошибку, так как начиная поиск со второй буквы текстовой строки, она не может ничего найти.

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

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

В Excel есть несколько функций, которые вычисляют центральную тенденцию в диапазоне данных: СРЕДНИЙ , МЕДИАН и РЕЖИМ . Наиболее часто получаемый показатель центральной тенденции – это простое среднее (среднее), и оно рассчитывается путем сложения группы чисел, а затем деления на количество этих чисел.

Вот как найти среднее арифметическое с помощью функции AVERAGE .

Примечание . Эти инструкции применимы к Excel 2019, 2016, 2013, 2010, Excel для Mac и Excel для Office 365.

Синтаксис и аргументы функции AVERAGE

Синтаксис функции относится к макету функции и включает имя функции, скобки, разделители запятых и аргументы. Синтаксис функции AVERAGE :

 = СРЗНАЧ (число1, число2, ... Number255)

Number1 (обязательно) – это данные, по которым функция должна находить среднее значение.
Число от 2 до 255 (необязательно) – это дополнительные данные, которые вы хотите включить в среднее вычисление. Максимально допустимое количество записей – 255.

Варианты ввода функции и ее аргументов включают в себя:

Ввод полной функции в ячейку листа
Ввод функции и аргументов с помощью диалогового окна «Функция»
Ввод функции и аргументов с помощью ярлыка Excel Средняя функция

Пример функции Excel AVERAGE

В Excel есть ярлык для входа в функцию AVERAGE , иногда называемую AutoAverage , из-за ее связи с более известной функцией AutoSum , расположенной на Главная вкладка на ленте .

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

Нажмите ячейку D4, , где отобразятся результаты формулы.
Нажмите Главная вкладку на ленте .
Нажмите стрелку вниз рядом с кнопкой Автосумма на ленте , чтобы открыть раскрывающееся меню.
Выберите Среднее в списке, чтобы включить функцию СРЕДНИЙ в ячейку D4.
Выделите ячейки A4 – C4 , чтобы ввести эти ссылки в качестве аргументов для функции, и нажмите клавишу Enter на клавиатуре.
Число 10 должно появиться в ячейке D4; это среднее из трех чисел ( 4 , 20 и 6) .

Лучшие практики для использования функции AVERAGE

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

Как AutoAverage выбирает диапазоны

Диапазон по умолчанию включает только ячейки, содержащие числа.
Функция СРЕДНЯЯ должна вводиться в нижней части столбца данных или в правом конце строки данных, поскольку она сначала ищет числовые данные выше, а затем слева.
Так как функция AVERAGE , по сути, угадывает диапазон, который она выбирает для аргумента Number; Вы должны всегда проверять точность, прежде чем нажимать клавишу Enter на клавиатуре, чтобы завершить функцию.

Пустые клетки против нулевых клеток

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

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

Выключить/включить нулевые ячейки (ПК с Windows)

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

Выключить/включить нулевые ячейки (Mac):

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

Поиск позиций ВСЕХ текстовых значений, удовлетворяющих критерию

Функция ПОИСКПОЗ() возвращает только одно значение. Если в списке присутствует несколько значений, удовлетворяющих критерию, то эта функция не поможет.

Рассмотрим список с повторяющимися значениями в диапазоне B66:B72 . Найдем все позиции значения Груши .

Значение Груши находятся в позициях 2 и 5 списка. С помощью формулы массива

=(«груши»=$B$66:$B$72)*(СТРОКА($B$66:$B$72)-СТРОКА($D$65))

можно найти все эти позиции. Для этого необходимо выделить несколько ячеек (расположенных вертикально), в Строке формул ввести вышеуказанную формулу и нажать CTRL+SHIFT+ENTER . В позициях, в которых есть значение Груши будет выведено соответствующее значение позиции, в остальных ячейках быдет выведен 0.

C помощью другой формулы массива

=НАИБОЛЬШИЙ((«груши»=$B$66:$B$72)*(СТРОКА($B$66:$B$72)-СТРОКА($D$65));СТРОКА()-СТРОКА($D$65))

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

Функция 23: FIND (НАЙТИ)

Функция FIND (НАЙТИ) находит текстовую строку внутри другой текстовой строки с учётом регистра.

Как можно использовать функцию FIND (НАЙТИ)?

Функция FIND (НАЙТИ) может найти текст внутри текстовой строки, учитывая регистр символов. Например:

Найти начальную позицию текста в текстовой строке.
Найти точные данные на листе.
Найти название улицы в адресе.

Синтаксис FIND (НАЙТИ)

Функция FIND (НАЙТИ) имеет вот такой синтаксис:

FIND(find_text,within_text,)НАЙТИ(искомый_текст;просматриваемый_текст;)

find_text (искомый_текст) – текст, который Вы ищете.
within_text (просматриваемый_текст) – текстовая строка, внутри которой происходит поиск.
start_num (нач_позиция) – если не указан, то поиск начнётся с первого символа.

Ловушки FIND (НАЙТИ)

Функция FIND (НАЙТИ) возвратит позицию первой совпадающей строки с учётом регистра. Для того, чтобы произвести поиск без учёта регистра, используйте функцию SEARCH (ПОИСК), которую мы уже рассматривали ранее в рамках марафона 30 функций Excel за 30 дней.
В аргументе find_text (искомый_текст) функции FIND (НАЙТИ) нельзя использовать символы подстановки. Если Вы все-таки хотите их использовать, то применяйте функцию SEARCH (ПОИСК).