Как в excel найти формулу: Поиск формул в Excel — Среда 31
Содержание
Как посчитать среднее значение в Excel: формула, функции, инструменты
Во время работы с числовыми данными в Эксель нередко перед пользователями встает такая задача, как подсчет среднего значения. Математически данное действие выполняется путем деления суммы всех чисел на их количество. А как это сделать в Excel? Давайте разбираться.
Содержание
- Информация в строке состояния
- Расчет среднего значения
- Использование арифметического выражения
- Инструменты на ленте
- Использование функции СРЗНАЧ
- Инструменты во вкладке “Формулы”
- Ввод функции в ячейку вручную
- Определение среднего значения по условию
- Заключение
Информация в строке состояния
Смотрите также: “Как найти обратную матрицу в Excel”
Пожалуй, это самый легкий и быстрый способ определения среднего значения. Для этого достаточно выделить диапазон, содержащий от двух ячеек и более, и среднее значение по ним сразу же отобразится в строке состояния программы.
Если данная информация недоступна, скорее всего, соответствующий пункт выключен в настройках. Чтобы обратно его включить, щелкаем правой кнопкой мыши по строке состояния, в открывшемся списке проверяем наличие флажка напротив строки “Среднее”. Поставить его в случае необходимости можно простым щелчком левой кнопки мыши.
Расчет среднего значения
Когда среднее значение нужно не только определить, но и зафиксировать в отдельной выбранной для этого ячейке, можно использовать несколько методов. Ниже мы подробно рассмотрим каждый из них.
Использование арифметического выражения
Как мы знаем, среднее значение равняется сумме чисел, разделенных на их количество. Данную формулу можно использовать и в Экселе.
- Встаем в нужную ячейку, ставим знак “равно” и пишем арифметическое выражение по следующем принципу:
=(Число1+Число2+Число3...)/Количество_слагаемых
.
Примечание: в качестве числа может быть указано как конкретное числовое значение, так и ссылка на ячейку. В нашем случае, давайте попробуем посчитать среднее значение чисел в ячейках B2,C2,D2 и E2.
Конечный вид формулы следующий:=(B2+E2+D2+E2)/4
. - Когда все готово, жмем Enter, чтобы получить результат.
Данный метод, безусловно хорош, но удобство его использования существенно ограничено объемом обрабатываемых данных, ведь на перечисление всех чисел или координат ячеек в большом массиве уйдет немало времени, к тому же, в этом случае не исключена вероятность допущения ошибки.
Инструменты на ленте
Данный метод основан на использовании специального инструмента на ленте программы. Вот как это работает:
- Выделяем диапазон ячеек с числовыми данными, для которых мы хотим определить среднее значение.
- Переходим во вкладку “Главная” (если находимся не в ней). В разделе инструментов “Редактирование” находим значок “Автосумма” и щелкаем по небольшой стрелке вниз рядом с ним. В раскрывшемся перечне кликаем по варианту “Среднее”.
- Сразу же под выделенным диапазоном отобразится результат, который и является средним значением по всем отмеченным ячейкам.
- Если мы перейдем в ячейку с результатом, то в строке формул увидим, какая функция была использована программой для расчетов – это оператор СРЗНАЧ, аргументами которого является выделенный нами диапазон ячеек.
Примечание: Если вместо вертикального выделения (столбца целиком или его части) будет выполнено горизонтальное выделение, то результат отобразится не под областью выделения, а справа от нее.
Данный метод, достаточно прост и позволяет быстро получить нужный результат. Однако помимо очевидных плюсов, есть у него и минус. Дело в том, что он позволяет вычислить усредненное значение только по ячейкам, расположенными подряд, причем, только в одном столбце или строке.
Чтобы было нагляднее, разберем следующую ситуацию. Допустим, у нас есть две заполненные данными строки. Мы хотим получить среднее значение сразу по двум строкам, следовательно, выделяем их и применяем рассмотренный инструмент.
В результате, мы получим средние значения под каждым столбцом, что тоже неплохо, если преследовалась именно такая цель.
Но если, все же, требуется определить среднее значение по нескольким строкам/столбцам или разбросанным в разных местах таблицы ячейкам, пригодятся методы, описанные далее.
Альтернативный способ использования “Среднее” на ленте:
- Переходим в первую же свободную ячейку после столбца или строки (в зависимости от структуры данных) и жмем кнопку расчета среднего значения.
- Вместо моментального вывода результата на этот раз программа предложит нам предварительно проверить диапазон ячеек, по которому будет считаться среднее значение, и в случае необходимости скорректировать его координаты.
- По готовности жмем клавишу Enter и получаем результат в заданной ячейке.
Использование функции СРЗНАЧ
С данной функцией мы уже успели познакомиться, когда перешли в ячейку с результатом расчета среднего значения. Теперь давайте научимся полноценно ею пользоваться.
- Встаем в ячейку, куда планируем выводить результат. Кликаем по значку “Вставить функци” (fx) слева от строки формул.
- В открывшемся окне Мастера функций выбираем категорию “Статистические”, в предлагаемом перечне кликаем по строке “СРЗНАЧ”, после чего нажимаем OK.
- На экране отобразится окно с аргументами функции (их максимальное количество – 255). Указываем в качестве значения аргумента “Число1” координаты нужного диапазона. Сделать это можно вручную, напечатав с клавиатуры адреса ячеек. Либо можно сначала кликнуть внутри поля для ввода информации и затем с помощью зажатой левой кнопки мыши выделить требуемый диапазон в таблице. При необходимости (если нужно отметить ячейки и диапазоны ячеек в другом месте таблицы) переходим к заполнению аргумента “Число2” и т.д. По готовности щелкаем OK.
- Получаем результат в выбранной ячейке.
- Среднее значение не всегда может быть “красивым” за счет большого количества знаков после запятой. Если нам такая детализация не нужна, ее всегда можно настроить. Для этого правой кнопкой мыши щелкаем по результирующей ячейке. В открывшемся контекстном меню выбираем пункт “Формат ячеек”.
- Находясь во вкладке “Число” выбираем формат “Числовой” и с правой стороны окна указываем количество десятичных знаков после запятой. В большинстве случаев, двух цифр более, чем достаточно. Также при работе с большими числами можно поставить галочку “Разделитель групп разрядов”. После внесение изменений жмем кнопку OK.
- Все готово. Теперь результат выглядит намного привлекательнее.
Инструменты во вкладке “Формулы”
В программе Excel есть специальная вкладка, отвечающая за работу с формулами. В случае с расчетом среднего значения, она тоже может пригодиться.
- Встаем в ячейку, в которой планируем выполнить расчеты. Переключаемся во вкладку “Формулы”. В разделе инструментов “Библиотека функций” щелкаем по значку “Другие функции”, в раскрывшемся перечне выбираем группу “Статистические”, затем – “СРЗНАЧ”.
- Откроется уже знакомое окно аргументов выбранной функци. Заполняем данные и жмем кнопку OK.
Ввод функции в ячейку вручную
Как и все остальные функции, формулу СРЗНАЧ с нужными аргументами можно сразу же прописать в нужной ячейке.
В общем, синтаксис функции СРЗНАЧ выглядит так:
=СРЗНАЧ(число1;число2;...)
В качестве аргументов могут выступать как ссылки на отельные ячейки (диапазоны ячеек), так и конкретные числовые значения.
=СРЗНАЧ(3;5;22;31;75)
Просто встаем в нужную ячейку и, поставив знак “равно”, пишем формулу, перечислив аргументы через символ “точка с запятой”. Вот, как это выглядит со ссылками на ячейки в нашем случае. Допустим, мы решили включить в подсчет всю первую строку и только три значения из второй:
=СРЗНАЧ(B2;C2;D2;E2;F2;G2;h3;B3;C3;D3)
Когда формула полностью готова, нажимаем клавишу Enter и получаем готовый результат.
Безусловно, такой метод нельзя назвать удобным, но иногда, при небольшом объеме данных, и он вполне может использоваться.
Определение среднего значения по условию
Смотрите также: “Подбор параметра в Excel: где находится, как сделать “
Помимо перечисленных выше методов, в Эксель также предусмотрена возможность расчета среднего значения по заданному пользователем условию. Как следует из описания, участвовать в общем подсчете будут только числа (ячейки с числовыми данными), соответствующие какому-то конкретному условию.
Допустим, нам нужно посчитать среднее значение только по положительным числам, т.е. тем, которые больше нуля. В этом случае, нас выручит функция СРЗНАЧЕСЛИ.
- Встаем в результирующую ячейку и жмем кнопку “Вставить функцию” (fx) слева от строки формул.
- В Мастере функций выбираем категорию “Статистические”, кликаем по оператору “СРЗНАЧЕСЛИ” и жмем ОК.
- Откроются аргументы функции, после заполнения которых кликаем OK:
- в значении аргумента “Диапазон” указываем (вручную или выделив с помощью левой кнопки мыши в самой таблице) требуемую область ячеек;
- в значении аргумента “Условие”, соответственно, задаем наше условие попадания ячеек из отмеченного диапазона в общий расчет. В нашем случае, это выражение “>0”. Вместо конкретного числа, в случае необходимости, в условии можно указать адрес ячейки, содержащей числовое значение.
- поле аргумента “Диапазон_усреднения” можно оставить пустим, так как его обязательное заполнение требуется только при работе с текстовыми данными.
- Среднее значение с учетом заданного нами условия отбора ячеек отобразилось в выдранной ячейке.
Заключение
Таким образом, в Экселе существует немало способов для нахождения среднего значения как по отдельным строкам и столбцам, так и по целым диапазонам ячеек, которые, к тому же, могут быть разбросаны по таблице. А использование того или иного метода определяется удобством и целесообразностью его использования в каждом конкретном случае.
Как найти циклическую ссылку в Excel
Главная » Программы
Добро пожаловать в очередную мою статью по работе с Excel. Здесь я покажу, как можно найти циклическую ссылку в Excel и убрать ее, чтобы все расчеты снова заработали. Коротко, по шагам, с картинками. Поехали!
Содержание
- О чем речь?
- Способ 1 – Панель формул
- Способ 2 – Стрелка трассировки
- Видео по теме
- Задать вопрос автору статьи
О чем речь?
При работе с формулами в Excel может возникнуть ситуация, когда формула ссылается сама на себя. Банальный пример: мы суммируем столбец чисел и по ошибке последнюю ячейку с самой формулой тоже прописываем в формулу. Как итог – в формуле содержится ссылка сама на себя (циклическая), а рассчитать такую сумму становится невозможным.
«Некоторые формулы содержат циклические ссылки…»
Этот пример очень простой. Обычно же при подготовке большого листа Excel с расчетами сама циклическая сущность может потеряться из глаза. И на этот счет разработчики все-таки попытались создать инструмент для поиска ссылок (и чего они не догадались просто в сообщении об ошибке сразу писать проблемную ячейку).
Способ 1 – Панель формул
- После получения показанной выше ошибки, просто закрываем ее на крестик (не на кнопку «ОК», о ней будет в следующем разделе). Ячейка с формулой должна быть выделена, при этом результат в ней может быть «странным» — это нормально, переходим к следующему шагу.
- В экселе переходим на вкладку «Формулы». Находим кнопку «Проверка ошибок», а в раскрывающемся списке «Циклические ссылки» (пункт активен, если в ячейке с формулой есть циклические ссылки).
В старых версиях это находилось почти тут же – «Формулы – Зависимости формул – Проверка наличия ошибок – Циклические ссылки».
- Обращаем внимание, что в этом пункте сразу же помечена ячейка с проблемной ссылкой. Наши пример простой, и здесь все очевидно. Но если что-то будет сложнее – будут показаны все проблемные ячейки. Пробегаемся по каждой из них до тех пор, пока пункт «Циклические ссылки» не перестанет быть активным.
Способ 2 – Стрелка трассировки
- Если помните, в первом способе мы просто закрывали диалоговое окно с ошибкой на крестик. В этот раз предлагаю нажать по кнопке «ОК».
- Должна появиться вот такая стрелка, которая и укажет на проблемную ячейку:
На рисунке выше проблема в ячейке B10, но она показывает на ячейку B9 (куда я просто приплюсовал еще раз формульную ячейку B10) – так можно исследовать все нужные ячейки и найти проблему. Как видите, в самой механике поиска ничего сложного нет, гораздо сложнее разобраться с сложных чужих таблицах и не поломать там логику.
Видео по теме
youtube.com/embed/zUaiuf_wKa0?feature=oembed» frameborder=»0″ allow=»accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture» allowfullscreen=»»>
Помогла ли вам эта статья?
83 4
Автор статьи
Ботан 800 статей
Мастер занудных текстов и технического слога. Мистер классные очки и зачётная бабочка. Дипломированный Wi-Fi специалист.
Узнать о формуле поиска Excel
Тепинг Крокер
Категории: Excel®
Теги: Excel Формула поиска
Иногда получение ценной или полезной информации из ваших данных немного похоже на поиск сокровищ: вы тратите много времени на решение головоломок и составление «карты» формул, которые, как мы надеемся, приведут вас к золотой жиле информации, скрытой внутри.
Функция НАЙТИ и ее близкая родственница ПОИСК — это функция, которая может приблизить вас к сокровищнице полезной информации, особенно в сочетании с другими функциями.
Давайте начнем с понимания основного синтаксиса FIND. НАЙТИ найдет указанную текстовую строку во второй текстовой строке, а затем вернет номер начальной позиции результата. Эти шаги применимы к Excel 2007-2013. Изображения были сделаны с использованием Excel 2013 в ОС Windows 7.
СИНТАКСИС: НАЙТИ(найти_текст, внутри_текста, [начальный_номер])
В этом примере мы создали формулу, которая будет искать текстовую строку TCFV в ячейке кода продукта. 3 возвращается в строке 35-41, потому что TCFV находится начиная с 3 rd символ кода продукта. Ошибка #ЗНАЧ! возвращается в строке 42, так как TCFV не найден в C42.
Важно отметить, что функция ПОИСК чувствительна к регистру. Если бы мы искали «tcfv» вместо «TCFV», как в следующем примере, результаты не были бы найдены.
Вот тут-то и пригодится ПОИСК, близкий родственник НАЙТИ. SEARCH не чувствителен к регистру и допускает подстановочные знаки в аргументе find_text.
Эта функция помогает нам находить строки текста среди других строк текста. Теперь давайте рассмотрим другие способы использования этой функции для создания чего-то действительно полезного.
Объединить НАЙТИ с ЕСЛИ и ЕОШИБКА для получения результатов True/False
Часто при поиске текста требуется отобразить результат в зависимости от того, было ли найдено совпадение. В этом примере результирующая формула сообщает нам, что все продукты со строкой TCFV в коде продукта подлежат отзыву, а все остальные продукты безопасны.
Если бы мы описали эту формулу на естественном языке, она могла бы выглядеть так: Если возникает ошибка из-за того, что функция НАЙТИ НЕ нашла строку «TCFV», тогда отобразите текст «Безопасно», в противном случае отобразите текст «В процессе отзыва», поскольку строка была найдена.
Существует много потенциальных применений этого метода, который позволяет вам выбирать определенные действия в зависимости от того, найдена ли ваша текстовая строка.
Объедините функцию FIND с функцией MID, чтобы получить только необходимую информацию
Если у вас есть данные, представляющие собой длинную строку символов, и вам полезна только часть этих символов, вы можете использовать функцию MID, чтобы извлечь только те, которые вам нужны, например, два числа, которые всегда следуют за тире в наших кодах продуктов.
Иногда, однако, ваши данные недостаточно согласованы, чтобы просто сказать Excel «вернуть символ 6 th через 8 th ». Наши коды продуктов в следующем примере всегда начинаются с «NW», но иногда перед дефисом есть еще четыре, а иногда и два символа. Итак, как мы можем вернуть только два символа после тире с помощью функции MID? СИНТАКСИС: MID( строка , начало, [ длина ] )
Вместо указания начального символа мы можем использовать функцию НАЙТИ в качестве начального аргумента. НАЙТИ вернет расположение символа тире. Мы добавим +1, чтобы указать, что мы хотим, чтобы начальное место MID было одним после местоположения тире.
Какими другими способами вы использовали функцию НАЙТИ?
PRYOR+ 7 ДНЕЙ БЕСПЛАТНОГО ОБУЧЕНИЯ
Курсы по обслуживанию клиентов, Excel, управлению персоналом, лидерству,
ОСАГО и многое другое. Нет кредитной карты. Без комментариев. Индивиды и команды.
НАЧАТЬ ПРОБНЫЙ ПЕРИОД
xltrail — Контроль версий электронных таблиц Excel
Большинство рабочих книг Excel содержат ошибки, которые в некоторых случаях приводят к неприятным «сюрпризам». Ошибки электронных таблиц бывают разных видов: некоторые из них легко обнаружить, но другие гораздо более тонкие: например, когда вы забыли обновить внешний источник данных или когда вы копируете формулу из ячейки выше, а не из ячейки слева. Или вы в конечном итоге считаете некоторые клетки дважды и т. д. и т. д.
Поскольку существует так много разных ошибок, эта запись в блоге посвящена ошибкам в формулах (поскольку их легко найти) и оставит другие типы ошибок для будущих сообщений в блоге.
Содержание
- Что такое ошибки в формулах?
- Перейти к специальному
- Ошибка проверки
- Запрос надстройки
- Автоматическая проверка ошибок
- Экспертная оценка
- Заключение
Что такое ошибки формул?
Как следует из названия, ошибки в формулах вызываются формулами или функциями, которые возвращают ошибку. Вот обзор:
-
#DIV/0!
: Число разделено на 0 или на пустую ячейку. -
#Н/Д
: значение недоступно для формулы или функции. Например. ВПР не находит соответствия. -
#ИМЯ?
: Часть текста не распознается в формуле. Например. вы используете именованный диапазон с опечаткой. -
#НОЛЬ!
: Пересечение двух областей, которые не пересекаются. -
#ЧИСЛО!
: Формула или функция содержит недопустимые числовые значения. Например. еслиIRR
не может найти результат. -
#ССЫЛКА!
: недопустимая ссылка на ячейку. Например. вы удалили ячейку, которая используется в другой формуле. -
#ЗНАЧ!
: Может произойти, если формула содержит ячейки с разными типами данных. Например, если вы добавляете две ячейки, одна из которых является числом, а другая — буквой.
Excel предлагает несколько встроенных способов поиска ошибок в формулах, давайте рассмотрим их по порядку:
Перейти к специальному
На вкладке Главная вашей ленты перейдите к Найти и выбрать
> Перейти к специальному. ..
(или с помощью Ctrl-G
и Alt-S
):
9000 3
затем выберите Формулы
и проверьте Ошибки
:
Когда вы нажмете OK
, Excel отформатирует ячейки с ошибками на активном листе серым цветом:
Ошибка Проверка
Вы также можете просмотреть ошибки более удобным способом (а не просто выделять их, как мы делали в предыдущем разделе):0115 Формулы вкладку на ленте и нажмите Проверка ошибок
в разделе Аудит формул
. Откроется следующее всплывающее окно, в котором вы можете нажать Далее
, чтобы перейти к следующей ошибке:
Надстройка Inquire
В более поздних версиях Excel Microsoft включила надстройку Inquire. Если вы не видите вкладку Запросить
на ленте, перейдите к Файл
> Параметры
> Надстройки
. Затем внизу под Управление
выберите Надстройки COM
и нажмите Перейти. ..
. Во всплывающем окне установите флажок рядом с Запросить
. Теперь должна появиться вкладка на ленте.
Когда на ленте появится вкладка Inquire
, щелкните Анализ рабочей книги
, и вы получите подробный анализ содержимого вашей рабочей книги. В качестве примера вы также можете перечислить свои ошибки в формуле:
Автоматическая проверка ошибок
По умолчанию Excel показывает ошибки в формулах (и многие другие), выделяя ячейку зеленым треугольником в левом верхнем углу ячейки. Выберите ячейку и нажмите на появившуюся кнопку трассировки ошибок. Это объяснит ошибку, а также предложит помощь по ней. Если ошибка ожидается, вы также можете игнорировать ее:
Чтобы контролировать, какие ошибки помечены этим зеленым треугольником, перейдите к Файл
> Параметры
> Формулы
:
Экспертная проверка
Экспертная проверка является эффективным способом уменьшения количества ошибок и хорошим дополнением к автоматической проверке ошибок. Экспертная оценка является стандартной практикой в разработке программного обеспечения (т. е. коллега просматривает ваши изменения до того, как они попадут в кодовую базу).
Для Excel это долгое время было сложной задачей, так как не было хороших решений для контроля версий и рецензирования книг Excel.
xltrail, решение, похожее на GitHub или SharePoint, делает задачу рецензирования изменений в книге Excel тривиальной: оно позволяет увидеть, что изменилось между двумя версиями файла, и делает видимыми изменения, которые могли произойти в скрытых листах или столбцах:
Например, на приведенном выше снимке экрана показано, как удаление одного столбца (выделено красным) привело к появлению множества #REF! 90 116 ошибок, которые легко обнаружить в процессе рецензирования.
Заключение
Мы рассмотрели несколько различных способов выявления ошибок в формулах в Microsoft Excel. Сообщите нам в комментариях ниже, какой метод вы предпочитаете или используете ли вы другой метод для обнаружения ошибок такого типа.