Как не учитывать значения в формуле эксель
Собираю отчет, столкнулся с такой задачкой, когда нужно чтобы среднее значение в ячейке выводилось без учета ячеек с нулями.
Если есть 3 ячейки и в 2-х из них стоят нули, то эксель делит на 3, а нужно чтобы не делил на 3, а делил только на заполненные больше нуля цифры.
Пример не прилагаю, думаю он не нужен. Если нужен, приложу.
Собираю отчет, столкнулся с такой задачкой, когда нужно чтобы среднее значение в ячейке выводилось без учета ячеек с нулями.
Если есть 3 ячейки и в 2-х из них стоят нули, то эксель делит на 3, а нужно чтобы не делил на 3, а делил только на заполненные больше нуля цифры.
Пример не прилагаю, думаю он не нужен. Если нужен, приложу. AleksFin
Собираю отчет, столкнулся с такой задачкой, когда нужно чтобы среднее значение в ячейке выводилось без учета ячеек с нулями.
Если есть 3 ячейки и в 2-х из них стоят нули, то эксель делит на 3, а нужно чтобы не делил на 3, а делил только на заполненные больше нуля цифры.
Пример не прилагаю, думаю он не нужен. Если нужен, приложу. Автор - AleksFin
Дата добавления - 07.06.2017 в 11:27
Спасибо за ответы, наверно прикладываю файл, возможно я не правильно вопрос построил, посмотрите пжл файл.
Спасибо за ответы, наверно прикладываю файл, возможно я не правильно вопрос построил, посмотрите пжл файл. AleksFin
Спасибо за ответы.
В оригинальную форму когда вставляю, выдает "слишком много аргументов".
Зажимаю ctrl и подбираю по одному нужные мне ячейки, это можно обойти как-нибудь?
Спасибо за ответы.
В оригинальную форму когда вставляю, выдает "слишком много аргументов".
Зажимаю ctrl и подбираю по одному нужные мне ячейки, это можно обойти как-нибудь? AleksFin
В оригинальную форму когда вставляю, выдает "слишком много аргументов".
Зажимаю ctrl и подбираю по одному нужные мне ячейки, это можно обойти как-нибудь? Автор - AleksFin
Дата добавления - 07.06.2017 в 12:20
Не, не так. Нужно понимать, почему Вы выбрали именно ячейки из 11,13,13,17 и 20 строк. Не нужно придумывать примеры, просто отрубите кусок своего файла, уберите конф. информацию, положите сюда и объясните как-то примерно так: "Если в столбце А находятся значения ХХХ, то по таким строкам нужно вычислить среднее значение по столбцу В, но не учитывая нулевые значения"
Предположу, что Вам нужно что-то примерно вот такое
Не, не так. Нужно понимать, почему Вы выбрали именно ячейки из 11,13,13,17 и 20 строк. Не нужно придумывать примеры, просто отрубите кусок своего файла, уберите конф. информацию, положите сюда и объясните как-то примерно так: "Если в столбце А находятся значения ХХХ, то по таким строкам нужно вычислить среднее значение по столбцу В, но не учитывая нулевые значения"
Предположу, что Вам нужно что-то примерно вот такое
работать не будет. Почитайте справку по функции СРЗНАЧЕСЛИ и все сами поймете Автор - _Boroda_
Дата добавления - 07.06.2017 в 12:46
AleksFin, не понятно, из несвязанного диапазона надо выдернуть среднее значение среди ненулевых значений? тогда
AleksFin, не понятно, из несвязанного диапазона надо выдернуть среднее значение среди ненулевых значений? тогда
=
Вот теперь файл хороший и правильный.
В следующий раз тоже примерно так и делайте, тогда решение сразу получите, а не через 2 часа, как сейчас
=
И да, для цены <> не обязательно, достаточно просто >, как у Михаила
=
Вот теперь файл хороший и правильный.
В следующий раз тоже примерно так и делайте, тогда решение сразу получите, а не через 2 часа, как сейчас
=
И да, для цены <> не обязательно, достаточно просто >, как у Михаила _Boroda_
=
Вот теперь файл хороший и правильный.
В следующий раз тоже примерно так и делайте, тогда решение сразу получите, а не через 2 часа, как сейчас
=
И да, для цены <> не обязательно, достаточно просто >, как у Михаила Автор - _Boroda_
Дата добавления - 07.06.2017 в 13:18
AleksFin, то ли пример неудачный, то ли я чего то не понимаю? как может быть цена на изделие рассчитываться как среднее значение. в вашем варианте да, изделие из одной детали, а если из двух? одна деталь 15 другая 1, Средняя цена 8? че за бред какой то? если сравнить односоставное изделие со средней ценой 15 и двухсоставное изделие с ценой 8. в чем "соль"? какой вывод из этого следует?
AleksFin, то ли пример неудачный, то ли я чего то не понимаю? как может быть цена на изделие рассчитываться как среднее значение. в вашем варианте да, изделие из одной детали, а если из двух? одна деталь 15 другая 1, Средняя цена 8? че за бред какой то? если сравнить односоставное изделие со средней ценой 15 и двухсоставное изделие с ценой 8. в чем "соль"? какой вывод из этого следует? Sancho
Обычно функция «Среднее» может помочь вам рассчитать среднее значение диапазона, включая нули, в Excel. Но здесь вы хотите исключить нули при применении функции Average. Как можно игнорировать нули при вычислении среднего значения?
Среднее / суммирование / подсчет диапазона данных без учета нулей с помощью Kutools for Excel
Усреднение диапазона данных без учета нулей с помощью формулы
Если вы используете Excel 2007/2010/2013, эта простая функция AVERAGEIF может помочь вам быстро и легко решить эту проблему.
1. Введите эту формулу = СРЗНАЧЕСЛИ (B2: B13; «<> 0») в пустой ячейке помимо ваших данных, см. снимок экрана:
Внимание: В приведенной выше формуле B2: B13 - это данные диапазона, которые вы хотите усреднить, исключая нули, вы можете изменить его по своему усмотрению. Если в диапазоне есть пустые ячейки, эта формула также усредняет данные, исключая пустые ячейки.
2, Затем нажмите Enter key, и вы получите результат, исключающий нулевые значения. Смотрите скриншот:
Примечание: Эта формула не вступит в силу в Excel 2003, если у вас Excel 2003, вы должны объединить Сумма и СЧЕТЕСЛИ функция, примените следующую формулу: =SUM(B2:B13)/COUNTIF(B2:B13,">0") .
Среднее / суммирование / подсчет диапазона данных без учета нулей с помощью Kutools for Excel
Если вы хотите суммировать / усреднять / подсчитывать, игнорируя нулевые ячейки, вы можете применить Kutools for Excel's Выбрать определенные ячейки утилита для выбора ненулевых ячеек, а затем просмотреть результат расчета в Строка состояния.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите диапазон, который вы хотите усреднить, нажмите Кутулс > Выберите > Выбрать определенные ячейки. Смотрите скриншот:
2. В появившемся диалоговом окне отметьте Ячейка , а затем выберите Не равный из первого раскрывающегося списка в Конкретный тип раздел и перейдите в правое текстовое поле, чтобы ввести 0 . Смотрите скриншот:
3. Нажмите Ok, и все ячейки, которые больше 0, были выбраны, и вы можете просмотреть среднее значение, сумму, результат подсчета в строке состояния. Смотрите скриншот:
Наконечник. Если вы хотите получить бесплатную пробную версию Выбрать конкретную ячейкуфункция s, пожалуйста, перейдите к бесплатной загрузке Kutools for Excel сначала, а затем перейдите к применению операции в соответствии с вышеуказанными шагами.
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина "Копейка".
Способ 1. Функция СУММЕСЛИ, когда одно условие
Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в "Копейку", например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:
Жмем ОК и вводим ее аргументы:
- Диапазон - это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае - это диапазон с фамилиями менеджеров продаж.
- Критерий - это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак - один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву "П", а заканчивается на "В" - критерий П*В. Строчные и прописные буквы не различаются.
- Диапазон_суммирования - это те ячейки, значения которых мы хотим сложить, т.е. нашем случае - стоимости заказов.
Способ 2. Функция СУММЕСЛИМН, когда условий много
Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для "Копейки"), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) - в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:
При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3-Условие3), и четвертую, и т.д. - при необходимости.
Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться - см. следующие способы.
Способ 3. Столбец-индикатор
Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в "Копейку" и от Григорьева, то в ячейке этого столбца будет значение 1, иначе - 0. Формула, которую надо ввести в этот столбец очень простая:
=(A2="Копейка")*(B2="Григорьев")
Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:
Способ 4. Волшебная формула массива
Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:
=СУММ((A2:A26="Копейка")*(B2:B26="Григорьев")*D2:D26)
После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter - тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.
Способ 4. Функция баз данных БДСУММ
В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев - ячейки, содержащие условия отбора - и указать затем этот диапазон функции как аргумент:
Существует набор данных. Мне нужно скопировать этот набор (и цифровые и текстовые данные) на другой лист за исключением строк, скрытых фильтрацией или вручную. По возможности это должно быть сделано стандартными формулами excel
Добавлено через 6 минут
формула должна быть типа
если не отфильтровано на листе1, то Лист1!В1=Лист2!В1
Добавлено через 1 минуту
Я знаю, что подобным свойством характеризуется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Но там нет простого приравнивания. Кроме того, она не работает с текстовыми данными
Составить ведомость, учитывающую скидки
Покупатели магазина пользуются 10% скидками, если покупка состоит более, чем из пяти наименований.
Отфильтрованные данные и их редактирование
Добрый день! используется builder postgres на форме DBGrid и DBNavigator как открыть.
Какие действия нужно произвести, чтобы сохранить ориентацию цилиндров, учитывающую смещение центра эллипса
Здраствуйте. Я хочу понять порядок операций для расчета позиций элементов распаложенных по элипсу.
Существует набор данных. Мне нужно скопировать этот набор (и цифровые и текстовые данные) на другой лист за исключением строк, скрытых фильтрацией или вручную. По возможности это должно быть сделано стандартными формулами excel
F5→выделить→только видимые ячейки, копировать, вставить.
С помощью формул вероятно тоже возможно, но тут нужно знать правило фильтрации строк.
Добавлено через 12 минут
Например, используйте функцию VBA (вставить код нужно обязательно в модуль в редакторе VBA - меню insert → module)
Нет. Только формулами. Правил фильтрации, которые придется мне использовать при решении конкретной задачи, я не знаю. Нужно отследить факт фильтрации строки и не принять ее во внимание при копировании данных
Проще всего использовать vba функцию указанную выше (возможно - существует аналог из стандартных функций).
PS Если в качестве аргумента будет указано несколько строк, функция выдаст результат по самой верхней строке диапазона.
Функция - это хорошо. Но может есть формульное решение? У меня создалось впечатление, что со стандартными формулами Excel работает быстрее.
Добавлено через 16 минут
Сделал через функцию - скорость расчетов упала раз в 10.
Вот пример. Данные здесь абстрактны. Среди них есть текстовые, которые тоже нужно отображать. Формула работает с текстом некорректно: для моего случая текст из не скрытых строк НУЖНО отображать.
Функция работает корректно. Но на моем количестве данных - до 10-20 тыс строк - медленно.
Если допустимо, то использовать дополнительный столбец с признаком скрыта/не скрыта строка и в формулах задействовать его значение
. или как минимум доработать функцию, чтобы она возвращала конечный результат: возвращала значение ячейки (она все равно передается параметром), либо пустую строку для скрытых строк
я подобные задачи решаю обычно кодом
--добавляю рабочий столбец, куда от конца таблицы записываю результаты проверки, чтобы не потерять заголовочные строки
--пишу в него итог фильтрации или скрытия
--далее фильтрую по нему и копирую на другой лист
---
оказывается не я одна применяю дополнительный столбик
Вообщем-то и я сделал дополнительный столбик. Но удобнее записывать его в начале массива данных, а не в конце. тогда он будет всегда доступен для фильтрации. Особенно если закрепить области.
Но проблемы скорости это не решило. Думал - существует какое-то решение использованием формул excel.
Пока буду так.
AndreA SN, дополнительный столбик делать необязательно - сгодится любой столбик с цифрами (ну или дополнительный столбец с функцией строка, номером, или вообще любой цифрой кроме нуля). Если функция промежуточные итоги при суммировании одной этой ячейки дает ноль, значит эта строка скрыта. Можно использовать функцию если с условием "если равно нолю, то не показываем, если не равно - показываем".
Тогда формула будет (тэгом не оформляю) =ЕСЛИ(НЕ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109; 'исходные данные'!ссылка_на_ячейку_с_цифрой )=0); 'исходные данные'!ссылка_на_отображаемую_ячейку ;"")
я не знаю, какой столбик будет с цифрами в следующий раз.
Добавлено через 6 минут
AleksSid, а вот с этим можно поэкспериментировать.
Поэкспериментируйте. Дополнительный столбик с цифрами, местоположение которого вы не можете предсказать, ищите в столбце А.
В 2016 версии доступно 16384 столбцов, можете выбрать любой больше с номером более 10000 и вбить туда единицу в каждом последующем файле. Например кодом ниже:
AndreA SN, наличие текста не мешает использовать функцию суммы, соответственно - если в строке обязательно будет хотя бы одно число (если нет - вбейте единицу в любой столбик), то сумма всей нескрытой строки будет не равна нулю, сумма скрытой - равно.
И с какой целью вам необходимо знать, где именно на вашем листе будут цифры - не вижу в этом практического прока.
Для примера использовал ваш файл, строки скопировал в количестве 50 401.
Преобразовал неструктурированную таблицу в список по ctrl+L, имя оставил стандартное - "Таблица1"
Формула приняла совсем упрощенный вид (указана в стиле ссылок R1C1, так как ее в таком виде можно легко скопировать на все ячейки):
Аналогично, если таблица оформлена как список, то становится неважно, где будет находится (при надобности) дополнительный столбец - лишь бы заголовок всегда совпадал.
Слишком много, чтобы проверять глазами. Если дальнейшая обработка производится Excel, то необходимости отображать исходную таблицу на другом листе нет, обрабатывать можно непосредственно исходную таблицу.
Мне кажется, что тут следует покопать в сторону того, полностью ли соответствует поставленная задача желаемой цели.
на самом деле порождены желанием понять - что происходит?
Я здесь не утруждал никого спецификой моей работы. Попытался вычленить маленький вопрос и узнать - что же можно изменить в том, что я уже сделал?
Потому что изменить можно всегда. Заметьте: я не задаю здесь вопросы типа :"Сделайте мне обработчик, чтобы было на все случаи жизни". Я пишу его сам. Про все - не знаю, но большинство охватываю.
Я занимаюсь биометрическими обработками, корреляционным и дисперсионным анализом. Но не теоретически (хотя теория статистики тоже от меня терпит), а на практических данных, по определению и на практике поступающих ко мне в свальном порядке. Что мне придет завтра и в каком порядке - не знает даже мой клиент.
Анализ этих данных, изучение самой возможности анализа данных в различных взаимосвязях, - и есть предмет этой моей работы.
Встроенными методиками не пользуюсь.
Поверьте: 10-20 тыс. строк - это мизер в моей работе. Речь не идет о ситуации, когда я получил данные и с ними - работаю. Нет. Речь идет о ситуации, когда я едва успеваю просмотреть данные, а уже должен сделать выводы о тенденциях, взаимосвязях, рангах, корреляциях, выдать профили и т.п. И всё это - в телефонном режиме. Отсюда повышенные требования к скорости обработки и обязательному учету результатов фильтрации. В этом вале данных тип данных в том или ином столбце для меня важен с точки зрения - вычисляемые или невычисляемые в нём данные? Если невычисляемые - то можно ли сделать качественный дисперсионный анализ или нельзя? Всё остальное идет под отсев выбросов и количественные анализы.
В итоге у меня коэффициенты корреляции, дисперсии, их достоверности и статистика признаков в таблицы записываются оптом.
Программа сугубо специфическая. Но я действительно поставил себе задачу максимально упростить работу с ней, наделив ее минимальной "соображалкой" по таким вопросам.
Кто-то сейчас скажет, что не стоит замахиваться на профессиональные статкомплексы. Но вот тут я не соглашусь в принципе. Я много шарился по этим статистикам и прочему доброделу. Вот только на все компы моих клиентов их не поставишь. А excel есть везде. Да и люблю контролировать процесс полностью. А у нас и "у них" есть вещи, принципиально различающиеся в методике расчетов. Так что делаю всё ручкамипо нашим стандартным методикам.
Наработки родились не вчера. Последний вариант является примерно 10 версией с качественными изменениями.
Как убрать знак минуса с чисел в Excel?
При работе с листом Excel иногда нам нужно удалить отрицательный знак из чисел для некоторых вычислений, то есть мы хотели бы преобразовать отрицательные числа в положительные. Для выполнения этого требования вам могут помочь следующие методы.
Удалите отрицательный знак из чисел с помощью функции ABS
Вот простая функция ABS, чтобы убрать отрицательный знак с чисел, сделайте следующее:
1. Пожалуйста, введите эту формулу: = ABS (A1) в пустую ячейку и нажмите Enter ключ, и отрицательный знак был удален сразу, если ячейка имеет отрицательное значение, см. снимок экрана:
2. Затем выберите ячейку F1 и перетащите маркер заполнения к ячейкам диапазона, которые вы хотите содержать эту формулу, и все отрицательные знаки были удалены из отрицательных чисел, см. Снимок экрана:
3. Затем вы должны скопировать и вставить эти формулы в другой диапазон, который вам нужен в качестве значения.
Преобразуйте сразу несколько отрицательных значений в положительные
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Удалите отрицательный знак с чисел с помощью команды "Найти и заменить"
В Excel, Найти и заменить Команда также может помочь вам найти отрицательный знак в диапазоне и заменить его ничем, и таким образом число станет положительным.
1. Выберите диапазон, в котором вы хотите удалить знак минуса.
2. Нажмите Главная > Найти и выбрать > Замените, см. снимок экрана:
3. В Найти и заменить диалог под Замените tab введите отрицательный знак – в Найти то, что текстовое поле и ничего не вводите, чтобы оставить Заменить текстовое поле пустое. Смотрите скриншот:
4, Затем нажмите Заменить все Кнопка, все отрицательные знаки были удалены сразу, а отрицательные числа были преобразованы в положительные. Смотрите скриншот:
5. Затем нажмите OK и закройте диалоговое окно «Найти и заменить».
Удалите отрицательный знак из чисел с помощью Kutools for Excel
Если вы установили Kutools for Excel, Его Изменить знак ценностей также может оказать вам услугу. С этим Изменить знак ценностей вы также можете выполнить следующие операции:
После установки Kutools for Excel, пожалуйста, сделайте следующее:
1. Выберите диапазон данных, в котором вы хотите удалить отрицательные знаки.
2. Нажмите Кутулс > Content > Изменить знак ценностей, см. снимок экрана:
3. В Изменить знак ценностей диалоговое окно, выберите Измените все отрицательные значения на положительные вариант, см. снимок экрана:
4, Затем нажмите Ok or Применить , все отрицательные числа были преобразованы в положительные, а также удалены отрицательные знаки.
Демо: измените положительные числа на отрицательные или наоборот с помощью Kutools for Excel
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!
Читайте также: