Как определить место в эксель
Рассмотрим диапазон значений, в который регулярно заносятся новые данные.
Диапазон без пропусков и начиная с первой строки
В случае, если в столбце значения вводятся, начиная с первой строки и без пропусков, то определить номер строки последней заполненной ячейки можно формулой: =СЧЁТЗ(A:A))
Формула работает для числовых и текстовых диапазонов (см. Файл примера )
Значение из последней заполненной ячейки в столбце выведем с помощью функции ИНДЕКС() : =ИНДЕКС(A:A;СЧЁТЗ(A:A))
Ссылки на целые столбцы и строки достаточно ресурсоемки и могут замедлить пересчет листа. Если есть уверенность, что при вводе значений пользователь не выйдет за границы определенного диапазона, то лучше указать ссылку на диапазон, а не на столбец. В этом случае формула будет выглядеть так: =ИНДЕКС(A1:A20;СЧЁТЗ(A1:A20))
Диапазон без пропусков в любом месте листа
Если список, в который вводятся значения расположен в диапазоне E8:E30 (т.е. не начинается с первой строки), то формулу для определения номера строки последней заполненной ячейки можно записать следующим образом: =СЧЁТЗ(E9:E30)+СТРОКА(E8)
Формула СТРОКА(E8) возвращает номер строки заголовка списка. Значение из последней заполненной ячейки списка выведем с помощью функции ИНДЕКС() : =ИНДЕКС(E9:E30;СЧЁТЗ(E9:E30))
Диапазон с пропусками (числа)
В случае наличия пропусков (пустых строк) в столбце, функция СЧЕТЗ() будет возвращать неправильный (уменьшенный) номер строки: оно и понятно, ведь эта функция подсчитывает только значения и не учитывает пустые ячейки.
Если диапазон заполняется числовыми значениями, то для определения номера строки последней заполненной ячейки можно использовать формулу =ПОИСКПОЗ(1E+306;A:A;1) . Пустые ячейки и текстовые значения игнорируются.
Так как в качестве просматриваемого массива указан целый столбец ( A:A ), то функция ПОИСКПОЗ() вернет номер последней заполненной строки. Функция ПОИСКПОЗ() (с третьим параметром =1) находит позицию наибольшего значения, которое меньше или равно значению первого аргумента (1E+306). Правда, для этого требуется, чтобы массив был отсортирован по возрастанию. Если он не отсортирован, то эта функция возвращает позицию последней заполненной строки столбца, т.е. то, что нам нужно.
Чтобы вернуть значение в последней заполненной ячейке списка, расположенного в диапазоне A2:A20 , можно использовать формулу: =ИНДЕКС(A2:A20;ПОИСКПОЗ(1E+306;A2:A20;1))
Диапазон с пропусками (текст)
В случае необходимости определения номера строки последнего текстового значения (также при наличии пропусков), формулу нужно переделать: =ПОИСКПОЗ("*";$A:$A;-1)
Пустые ячейки, числа и текстовое значение Пустой текст ("") игнорируются.
Диапазон с пропусками (текст и числа)
Если столбец содержит и текстовые и числовые значения , то для определения номера строки последней заполненной ячейки можно предложить универсальное решение: =МАКС(ЕСЛИОШИБКА(ПОИСКПОЗ("*";$A:$A;-1);0); ЕСЛИОШИБКА(ПОИСКПОЗ(1E+306;$A:$A;1);0))
Функция ЕСЛИОШИБКА() нужна для подавления ошибки возникающей, если столбец A содержит только текстовые или только числовые значения.
Другим универсальным решением является формула массива : =МАКС(СТРОКА(A1:A20)*(A1:A20<>""))
После ввода формулы массива нужно нажать CTRL + SHIFT + ENTER . Предполагается, что значения вводятся в диапазон A1:A20 . Лучше задать фиксированный диапазон для поиска, т.к. использование в формулах массива ссылок на целые строки или столбцы является достаточно ресурсоемкой задачей.
Значение из последней заполненной ячейки, в этом случае, выведем с помощью функции ДВССЫЛ() : =ДВССЫЛ("A"&МАКС(СТРОКА(A1:A20)*(A1:A20<>"")))
Как обычно, после ввода формулы массива нужно нажать CTRL + SHIFT + ENTER вместо ENTER .
СОВЕТ: Как видно, наличие пропусков в диапазоне существенно усложняет подсчет. Поэтому имеет смысл при заполнении и проектировании таблиц придерживаться правил приведенных в статье Советы по построению таблиц .
Основное назначение этой функции в том, чтобы искать позицию заданного элемента в наборе значений. Чаще всего она применяется для поиска порядкового номера ячейки в диапазоне, где лежит нужное нам значение.
Синтаксис этой функции следующий:
=ПОИСКПОЗ( Что_ищем ; Где_ищем ; Режим_поиска )
- Что_ищем - это значение, которое надо найти
- Где_ищем - это одномерный диапазон или массив (строка или столбец), где производится поиск
- Режим_поиска - как мы ищем: точно (0), с округлением в большую строну (-1) или в меньшую сторону (1)
Давайте рассмотрим несколько полезных вариантов ее применения на практике.
Точный поиск
Классический сценарий - поиск точного текстового совпадения для нахождения позиции нужного нам текста или числа в списке:
Поиск первой или последней текстовой ячейки
Если в качестве искомого значения задать звездочку, то функция будет искать первую ячейку с текстом и выдавать её позицию. Для поиска последней текстовой ячейки можно изменить третий аргумент Режим_поиска с нуля на минус 1:
Числа и пустые ячейки в этом случае игнорируются.
Поиск ближайшего числа или даты
Если последний аргумент задать равным 1 или -1, то можно реализовать поиск ближайшего наименьшего или наибольшего числа. Таблица при этом обязательно должна быть отсортирована по возрастанию или убыванию соответственно. В общем и целом, это чем-то похоже на интервальный просмотр у функции ВПР (VLOOKUP) , но там возможен только поиск ближайшего наименьшего, а здесь - есть выбор.
Например, нам нужно выбрать генератор из прайс-листа для расчетной мощности в 47 кВт. Если последний аргумент задать равным 1 и отсортировать таблицу по возрастанию, то мы найдем ближайшую наименьшую по мощности модель (Зверь):
Если же третий аргумент равен -1 и таблица отсортирована по убыванию, то мы найдем ближайшую более мощную модель (Бомба):
Связка функций ПОИСКПОЗ и ИНДЕКС
Очень часто функция ПОИСКПОЗ используется в связке с другой крайне полезной функцией - ИНДЕКС (INDEX) , которая умеет извлекать данные из диапазона по номеру строки-столбца, реализуя, фактически, "левый ВПР".
Так, в предыдущем примере получить не номер, а название модели генератора можно очень легко:
Ну, и поскольку Excel внутри хранит и обрабатывает даты как числа, то подобный подход на 100% работает и с датами. Например, мы можем легко определить на каком этапе сейчас находится наш проект:
Принципиальное ограничение функции ПОИСКПОЗ состоит в том, что она умеет искать только в одномерных массивах (т.е. строчке или столбце), но никто не запрещает использовать сразу два ПОИСКПОЗа вложенных в ИНДЕКС, чтобы реализовать двумерный поиск по строке и столбцу одновременно:
ПРИМЕР 1. Необходимо определить среднее значение продаж, превышающее 70000 рублей.
Ставим равно (=) и пишем функцию СРЗНАЧЕСЛИ. Открывает круглую скобку и вводим параметры функции.
Мы будем выполнять оценку и вычислять среднее число для одного и того же диапазона ячеек. С (С2 по С7) в столбце продажи.
Вводим точку с запятой, указываем в кавычках условие, по которому будет оцениваться диапазон. Кавычки нужны для того, чтобы Excel правильно интерпретировал оператор и аргументы функции. В нашем случае это больше > 70000 рублей.
Нажимаем клавишу Enter и нам показывает готовый результат продаж в среднем. Средний показатель продаж, превышающих 70000 рублей составляет 79488 рублей.
Функция оценивает сколько ячеек в столбце продажи соответствуют условию >70000. Таких ячеек 3 (С3, С5, С7). Затем функция вычисляет для них усредненный показатель.
Стандартный способ вычисления
Самый простой и известный способ найти среднее арифметическое набора чисел — это воспользоваться специальной кнопкой на ленте Microsoft Excel. Выделяем диапазон чисел, расположенных в столбце или в строке документа. Находясь во вкладке «Главная», жмем на кнопку «Автосумма», которая расположена на ленте в блоке инструментов «Редактирование». Из выпадающее списка выбираем пункт «Среднее».
После этого, с помощью функции «СРЗНАЧ», производится расчет. В ячейку под выделенным столбцом, или справа от выделенной строки, выводится средняя арифметическая данного набора чисел.
Этот способ хорош простотой и удобством. Но, у него имеются и существенные недостатки. С помощью этого способа можно произвести подсчет среднего значения только тех чисел, которые располагаются в ряд в одном столбце, или в одной строке. А вот, с массивом ячеек, или с разрозненными ячейками на листе, с помощью этого способа работать нельзя.
Например, если выделить два столбца, и вышеописанным способом вычислить среднее арифметическое, то ответ будет дан для каждого столбца в отдельности, а не для всего массива ячеек.
Вычисление с помощью Мастера функций
Для случаев, когда нужно подсчитать среднюю арифметическую массива ячеек, или разрозненных ячеек, можно использовать Мастер функций. Он применяет все ту же функцию «СРЗНАЧ», известную нам по первому методу вычисления, но делает это несколько другим способом.
Кликаем по ячейке, где хотим, чтобы выводился результат подсчета среднего значения. Жмем на кнопку «Вставить функцию», которая размещена слева от строки формул. Либо же, набираем на клавиатуре комбинацию Shift+F3.
Запускается Мастер функций. В списке представленных функций ищем «СРЗНАЧ». Выделяем его, и жмем на кнопку «OK».
Открывается окно аргументов данной функции. В поля «Число» вводятся аргументы функции. Это могут быть как обычные числа, так и адреса ячеек, где эти числа расположены. Если вам неудобно вводить адреса ячеек вручную, то следует нажать на кнопку расположенную справа от поля ввода данных.
После этого, окно аргументов функции свернется, а вы сможете выделить ту группу ячеек на листе, которую берете для расчета. Затем, опять нажимаете на кнопку слева от поля ввода данных, чтобы вернуться в окно аргументов функции.
Если вы хотите подсчитать среднее арифметическое между числами, находящимися в разрозненных группах ячеек, то те же самые действия, о которых говорилось выше, проделывайте в поле «Число 2». И так до тех пор, пока все нужные группы ячеек не будут выделены.
После этого, жмите на кнопку «OK».
Результат расчета среднего арифметического будет выделен в ту ячейку, которую вы выделили перед запуском Мастера функций.
Панель формул
Существует ещё третий способ запустить функцию «СРЗНАЧ». Для этого, переходим во вкладку «Формулы». Выделяем ячейку, в которой будет выводиться результат. После этого, в группе инструментов «Библиотека функций» на ленте жмем на кнопку «Другие функции». Появляется список, в котором нужно последовательно перейти по пунктам «Статистические» и «СРЗНАЧ».
Затем, запускается точно такое же окно аргументов функции, как и при использовании Мастера функций, работу в котором мы подробно описали выше.
Дальнейшие действия точно такие же.
Вычисление среднего значения через встроенный инструмент в Excel
Выполнить задачу можно выделив нужный свод данных и на главной странице кликнув на «Функции» и выбрав «Среднее».
Готово система добавила ниже ячейку со средним значением.
Если использовать функцию сразу в нескольких столбцов, то система доставит ячейки под каждым.
Когда данные значение как отдельная цифра, то выделив значения в нижней части экрана будет указано нужное значение.
Среднее значение через формулу в Эксель
Сделать задачу также можно через стандартную формулу для чего прописываем в ячейке:
Что возвращает функция
Возвращает число, соответствующее среднему арифметическому от заданного диапазона данных.
Синтаксис
=AVERAGE(number1, [number2], …) – английская версия
=СРЗНАЧ(число1;[число2];…) – русская версия
Примеры использования функции СРЗНАЧ в Excel
Функция СРЗНАЧ относится к группе «Статистические». Поэтому для вызова данной функции в Excel необходимо выбрать инструмент: «Формулы»-«Библиотека функций»-«Другие функции»-«Статистические»-«СРЗНАЧ». Или вызвать диалоговое окно «Вставка функции» (SHIFT+F3) и выбрать из выпадающего списка «Категория:» опцию «Статистические». После чего в поле «Выберите функцию:» будет доступен список категории со статистическими функциями где и находится СРЗНАЧ, а также СРЗНАЧА.
Если есть диапазон ячеек B2:B8 с числами, то формула =СРЗНАЧ(B2:B8) будет возвращать среднее значение заданных чисел в данном диапазоне:
Синтаксис использования следующий: =СРЗНАЧ(число1; [число2]; …), где первое число обязательный аргумент, а все последующие аргументы (вплоть до числа 255) необязательны для заполнена. То есть количество выбранных исходных диапазонов не может превышать больше чем 255:
Аргумент может иметь числовое значение, быть ссылкой на диапазон или массив. Текстовое и логическое значение в диапазоне полностью игнорируется.
Аргументы функции СРЗНАЧ могут быть представлены не только числами, но и именами или ссылками на конкретный диапазон (ячейку), содержащий число. Учитывается логическое значение и текстовое представление числа, которое непосредственно введено в список аргументов.
Если аргумент представлен ссылкой на диапазон (ячейку), то его текст или логическое значение (ссылка на пустую ячейку) игнорируется. При этом ячейки, которые содержат нуль, учитываются. Если аргумент содержит ошибки или текст, который не может быть преобразован в число, то это приводит к общей ошибке. Для учета логических значений и текстового представления чисел необходимо в расчетах использовать функцию СРЗНАЧА, о которой пойдет разговор дальше.
Результат выполнения функции в примере на картинке ниже – это число 4, т.к. логические и текстовые объекты игнорируются. Поэтому:
(5 + 7 + 0 + 4) / 4 = 4
При вычислениях средних значений необходимо учитывать разницу между пустой ячейкой и ячейкой, содержащей нулевое значение, особенно если в диалоговом окне Excel снят флажок «Показывать нули в ячейках, которые содержат нулевые значения». Когда флажок установлен, пустые ячейки игнорируются, а нулевые значения нет. Чтобы снять или установить этот флаг необходимо открыть вкладку «Файл», далее нажать на «Параметры» и выбрать в категории «Дополнительно» группу «Показать параметры для следующего листа», где возможно установить флажок:
Результаты еще 4-х задач сведены в таблицу ниже:
Использование арифметического выражения
Как мы знаем, среднее значение равняется сумме чисел, разделенных на их количество. Данную формулу можно использовать и в Экселе.
- Встаем в нужную ячейку, ставим знак “равно” и пишем арифметическое выражение по следующем принципу:
=(Число1+Число2+Число3. )/Количество_слагаемых .
Примечание: в качестве числа может быть указано как конкретное числовое значение, так и ссылка на ячейку. В нашем случае, давайте попробуем посчитать среднее значение чисел в ячейках B2,C2,D2 и E2.
Конечный вид формулы следующий: =(B2+E2+D2+E2)/4 . - Когда все готово, жмем Enter, чтобы получить результат.
Данный метод, безусловно хорош, но удобство его использования существенно ограничено объемом обрабатываемых данных, ведь на перечисление всех чисел или координат ячеек в большом массиве уйдет немало времени, к тому же, в этом случае не исключена вероятность допущения ошибки.
Инструменты на ленте
Данный метод основан на использовании специального инструмента на ленте программы. Вот как это работает:
- Выделяем диапазон ячеек с числовыми данными, для которых мы хотим определить среднее значение.
- Переходим во вкладку “Главная” (если находимся не в ней). В разделе инструментов “Редактирование” находим значок “Автосумма” и щелкаем по небольшой стрелке вниз рядом с ним. В раскрывшемся перечне кликаем по варианту “Среднее”.
- Сразу же под выделенным диапазоном отобразится результат, который и является средним значением по всем отмеченным ячейкам.
- Если мы перейдем в ячейку с результатом, то в строке формул увидим, какая функция была использована программой для расчетов – это оператор СРЗНАЧ, аргументами которого является выделенный нами диапазон ячеек.
Примечание: Если вместо вертикального выделения (столбца целиком или его части) будет выполнено горизонтальное выделение, то результат отобразится не под областью выделения, а справа от нее.
Данный метод, достаточно прост и позволяет быстро получить нужный результат. Однако помимо очевидных плюсов, есть у него и минус. Дело в том, что он позволяет вычислить усредненное значение только по ячейкам, расположенными подряд, причем, только в одном столбце или строке.
Чтобы было нагляднее, разберем следующую ситуацию. Допустим, у нас есть две заполненные данными строки. Мы хотим получить среднее значение сразу по двум строкам, следовательно, выделяем их и применяем рассмотренный инструмент.
В результате, мы получим средние значения под каждым столбцом, что тоже неплохо, если преследовалась именно такая цель.
Но если, все же, требуется определить среднее значение по нескольким строкам/столбцам или разбросанным в разных местах таблицы ячейкам, пригодятся методы, описанные далее.
Альтернативный способ использования “Среднее” на ленте:
- Переходим в первую же свободную ячейку после столбца или строки (в зависимости от структуры данных) и жмем кнопку расчета среднего значения.
- Вместо моментального вывода результата на этот раз программа предложит нам предварительно проверить диапазон ячеек, по которому будет считаться среднее значение, и в случае необходимости скорректировать его координаты.
- По готовности жмем клавишу Enter и получаем результат в заданной ячейке.
Ввод функции в ячейку вручную
Как и все остальные функции, формулу СРЗНАЧ с нужными аргументами можно сразу же прописать в нужной ячейке.
В общем, синтаксис функции СРЗНАЧ выглядит так:
В качестве аргументов могут выступать как ссылки на отельные ячейки (диапазоны ячеек), так и конкретные числовые значения.
Просто встаем в нужную ячейку и, поставив знак “равно”, пишем формулу, перечислив аргументы через символ “точка с запятой”. Вот, как это выглядит со ссылками на ячейки в нашем случае. Допустим, мы решили включить в подсчет всю первую строку и только три значения из второй:
Когда формула полностью готова, нажимаем клавишу Enter и получаем готовый результат.
Безусловно, такой метод нельзя назвать удобным, но иногда, при небольшом объеме данных, и он вполне может использоваться.
Соответствие английских и русских формул в Excel
Функции баз данных — Database
Функции даты и времени — Date & Time
Инженерные функции — Engineering
Финансовые функции — Financial
Проверка свойств и значений и Информационные функции — Information
Логические функции — Logical
Ссылки и массивы — Lookup & Reference
Математические и тригонометрические функции — Math & Trig
Статистические функции — Statistical
Текстовые функции — Text
Заключение
В экселе существует немало способов для нахождения среднего значения как по отдельным строкам и столбцам, так и по целым диапазонам ячеек, которые, к тому же, могут быть разбросаны по таблице. А использование того или иного метода определяется удобством и целесообразностью его использования в каждом конкретном случае.
Функции МАКС и МИН прекрасно подходят для поиска наибольшего и наименышего значения в диапазоне данных. Но если нужно найти несколько максимальных или минимальных значений для этой задачи Excel предлагает несколько интересных функций: НАИБОЛЬШИЙ, НАИМЕНЬШИЙ, РАНГ и РАНГ.СР.
Пример выборки нескольких наибольших значений в Excel
Ниже на рисунке представлены результаты турниру по боулингу. Участники в турнирной таблице должны быть отсортированы по возрастанию по имени. Сложно проверить, который из них является победителем. Допустим нам необходимо узнать, которые участники заняли первые 3 места и какие их результаты. Ниже указанная формула, которая возвращает третье наибольшее число со списка результатов:
В формуле использованной для поиска имени участника используется комбинация функций ИНДЕКС и ПИСКПОЗ:
Функция НАИБОЛЬШИЙ и НАИМЕНЬШИЙ используются для поиска порядкового наибольшего и наименьшего значения в списке данных. В первом аргументе функции НАИБОЛЬШИЙ, как и в функции МАКС указывается ссылка на исходный диапазон просматриваемых значений. Однако отличие между ними заключается в том, что функция НАИБОЛЬШИЙ имеет еще один обязательный для заполнения аргумент «К» в котором можно указать порядковый номер возвращаемого наибольшего значения (второе наибольшее, третье… и т.д.).
Если исходный диапазон данных содержит одинаковые максимальные значения, тогда функция НАИБОЛЬШИЙ и НАИМЕНЬШИЙ возвращает тот самый результат для порядковых максимальных и минимальных значений (K и K+1). Например, если бы два участника получили одинаковый результат по 588 пунктов каждый, тогда формулы =НАИБОЛЬШИЙ($B$2:$B$13;1) и =НАИБОЛЬШИЙ($B$2:$B$13;2) возвращали бы одно и тоже число 558.
В ячейке E2 используется функция СТРОКА(A1) определяющая значение аргумента «К». Функция строка возвращает номер строки листа для ячейки, указанной в аргументе – в данном примере строка №1. Как второй аргумент функции НАИБОЛЬШИЙ можно было бы просто указать число 1, однако благодаря функции СТРОКА(A1) номер строки листа будет увеличиваться автоматически во время копирования формулы в очередные ячейки в низ от начальной. Ссылка на ячейку A1 является относительной, когда потом формула будет скопирована в ячейку E3, функция будет уже иметь следующий вид: СТРОКА(A2). С таким аргументом функция уже будет возвращать значение 2, а функция НАИБОЛЬШИЙ в ячейке E3 возвратит второе наибольшее значение из столбца «Результат» в исходном списке данных.
Формула ранжирования в Excel для выборки наибольших значений
В данном примере принципиальным является использование функции НАИБОЛЬШИЙ, так как мы анализируем результаты игры в боулинг. А в боулинге чем больше число пунктов, тем лучше результат. Если же бы мы анализировали другой вид спорта где вместо балов был список результатов времени в турнирной таблице, то в таком случае победителем будет тот участник, у которого наименьшее время. Турнирная таблица с временными результатами изображена ниже на рисунке. Таблица содержит дополнительный столбец с номером места каждого участника, а в нем использована формула следующая:
Чтобы узнать какое место занял участник с именем «Волчок Геннадий» необходимо заполнить аргументы для функции РАНГ. В первой ячейке следует указать время текущего участника (ползучем исходное время относительной ссылкой из ячейки B2), а во втором аргументе указать абсолютную ссылку на диапазон просматриваемых исходных значений $B$2:$B$13. В третьем аргументе указывается порядок сортировки. В данном примере это число 1, так как на первом месте должно быть наименьше время, то есть по возрастанию. Если бы на первом месте должно было бы быть наибольшее значение просматриваемого исходного диапазона ячеек, в таком случае в третьем аргументе функции РАНГ следует указывать число 0.
В отличии от функций НАИБОЛЬШИЙ и НАИМЕНЬШИЙ функция РАНГ возвращает фактический порядковый номер для каждого значения. Другими словами, функция РАНГ возвращает номер места исходного значения в списке просматриваемого диапазона, который отсортирован с учетом последнего аргумента функции. Чтобы получить не текущий номер, а значение найденной определенной ячейки следует использовать комбинацию функций ИНДЕКС и ПОИСКПОЗ, подобно как в случае с формулой, которая ищет имена участников.
Формула в ячейке F3 возвращает время участника, который занял третье место – выглядит следующим образом:
Если просматриваемый диапазон исходных значений содержит два или более одинаковых значения, функция РАНГ два раза подряд возвращает один и тот же результат. Например, если у двоих участников одно и тоже время результата 20:35, в таком случае функция РАНГ для обоих возвращает в результате вычислений число 1. Следующее большее очередное время в списке результатов получит третье место. Ни одно значение не получит в таком случае второго места, так как два одинаковы исходных значения занимают первое и второе место в ранжировании – одновременно:
Функция РАНГ.СР для ранжирования списка с повторяющимися значениями
В программе Excel версии 2010 и старше появились две новые функции ранжирования значений: РАНГ.РВ и РАНГ.СР, в которых по-разному решается проблема с одинаковыми значениями в диапазоне ячеек. Формула РАНГ.РВ – это аналог функции РАНГ, она возвращает тот же самый результат при таких же условиях с теми же самыми одинаковыми ячейками. А вот функция РАНГ.СР возвращает средний результат вычисления для всех одинаковых значений.
Допустим в таблице находятся результаты четырех участников с одинаковым вторым наименьшим показателем времени 21:38:
Для наилучшего времени в данной таблице функция РАНГ.СР естественно возвращает номер 1, а для четырех последующих наилучших показателей времени возвращено дробное значение 3,5. Эти четыре участника одновременно занимают места: 2, 3, 4 и 5, в итоге все места разделены между ними поровну, поэтому каждый участник получил по 3,5 балла. То есть 2+3+4+5/4=3,5.
Читайте также: