Загрузка...Набор значений, которые могут быть введены в ячейку Excel, ограничен: решение проблемы
Набор значений, которые могут быть введены в ячейку Excel, ограничен: решение проблемы
Иногда при работе с таблицами в редакторе Excel появляется уведомление, что «набор значений, которые могут быть введены в ячейку, ограничен». Самая главная проблема в том, что из-за этого вы не можете ввести нужную вам информацию. На самом деле ничего страшного тут нет. Это ситуацию можно исправить в несколько шагов. В этой статье мы подробно рассмотрим, из-за чего именно появляется это окно и что можно сделать в этой ситуации.
Предположим, у вас есть ряд данных, включая положительные и отрицательные значения, и здесь я расскажу о некоторых способах найти наименьшее положительное значение (которое больше 0) в Excel.
Найдите и выберите минимальное значение больше нуля с помощью Kutools for Excel 
Найдите и выберите минимальное значение больше нуля с помощью формулы
В Excel вы можете использовать формулу массива, чтобы найти наименьшие положительные значения.
Выберите пустую ячейку и введите эту формулу =MIN(IF(A1:E10>0,A1:E10)) в него и введите Shift + Ctrl + Enter ключи, чтобы получить наименьшее положительное значение в указанном диапазоне данных.
Наконечник: В формуле A1: E10 — это диапазон данных, из которого вы хотите найти наименьшее положительное значение.
Найдите минимальное значение больше нуля с помощью Kutools for Excel
Если вы плохо запоминаете формулы, вы можете использовать Kutools for ExcelАвтора Выбрать определенные ячейки полезности и Выберите ячейки с максимальным и минимальным значением утилита для поиска и выбора минимального положительного значения.
После бесплатная установка Kutools for Excel, сделайте следующее:
1. Выберите диапазон данных и щелкните Kutools > Выберите > Выбрать определенные ячейки. Смотрите скриншот:
2. Затем в Выбрать определенные ячейки диалог, проверьте Ячейка под Тип выбора раздел, выберите Менее под Конкретный тип раздел и введите 0 в следующее поле. Нажмите Ok > OK закрыть диалог. Затем выбираются все отрицательные числа. см. снимки экрана ниже:
3. Нажмите Удалить клавиша для удаления всех выбранных отрицательных значений. Смотрите скриншот:
4. Затем выберите положительные значения и нажмите Kutools > Выберите > Выберите ячейки с максимальным и максимальным значением. Смотрите скриншот:
5. Затем в Выберите ячейки с максимальным и минимальным значением диалоговом окне выберите тип ячейки, в которой вы хотите просмотреть, и отметьте Минимальное значение вариант и выберите, чтобы найти все ячейки или только первую появляющуюся. Затем нажмите Ok. Смотрите скриншот:
Работы С Нами Выбрать определенные ячейки утилита, вы можете выбирать целые строки или столбцы на основе определенных значений. Щелкните здесь, чтобы узнать больше о выборе конкретных ячеек.
Работы С Нами Выберите ячейки с максимальным и минимальным значением, вы можете выбрать минимальное или максимальное значение в ячейках формулы, ячейках значений или в ячейках формулы и значений, а также вы можете выбрать первую отображаемую ячейку или все минимальные или максимальные ячейки. Нажмите здесь, чтобы узнать больше о Kutools for Excel’s Select Cells with Max & Min Value.
Как рассчитать стандартное отклонение?
Итак, теперь мы знаем, о чем говорит цифра стандартного отклонения. Давайте разберемся, как она считается.
Рассмотрим набор данных от 10 до 70 с шагом 10. Как видите, я уже рассчитал для них значение стандартного отклонения с помощью функции СТАНДОТКЛОН в ячейке H2 (оранжевым).
Ниже описаны шаги, которые предпринимает Excel, чтобы прийти к цифре 21,6.
Обратите внимание, что все расчеты визуализированы, для лучшего понимания. На самом деле в Excel расчет происходит мгновенно, оставляя все шаги за кулисами.
Для начала Excel находит среднее значение выборки. В нашем случае, среднее получилось равным 40, которое на следующем шаге отнимают от каждого значения выборки. Каждую полученную разницу возводят в квадрат и суммируют. У нас получилась сумма равная 2800, которую необходимо разделить на количество элементов выборки минус 1. Так как у нас 7 элементов, получается необходимо 2800 разделить на 6. Из полученного результата находим квадратный корень, это цифра будет стандартным отклонением.
Для тех, кому не совсем ясен принцип расчета стандартного отклонения с помощью визуализации, привожу математическую интерпретацию нахождения данного значения.
Оптимальное распределение продавцов по торговым точкам
Задание. Существует 4 продавца А1, А2, А3, А4 и 4 торговые точки В1, В2, В3, В4. Эффективность работы продавцов на торговых точках задаётся следующей матрицей:
| 9 | 3 | 4 | 8 |
| 4 | 6 | 7 | 11 |
| 5 | 8 | 8 | 4 |
| 6 | 12 | 15 | 9 |
Найти оптимальное распределение продавцов по торговым точкам.
Поскольку задана матрица эффективности, то искать необходимо максимальные значения, следовательно, целевая функция стремится к максимуму. Именно поэтому при решении выбираем вид Максимальная прибыль .
Модифицируем матрицу умножением всех элементов на (-1) и затем сложением их с максимальным элементом матрицы (15) так, чтобы матрица не содержала бы отрицательных элементов:
| 6 | 12 | 11 | 7 |
| 11 | 9 | 8 | 4 |
| 10 | 7 | 7 | 11 |
| 9 | 3 | 6 |
Шаг №1.
1. Проводим редукцию матрицы по строкам. В связи с этим во вновь полученной матрице в каждой строке будет как минимум один ноль.
| 6 | 5 | 1 | 6 | |
| 7 | 5 | 4 | 4 | |
| 3 | 4 | 7 | ||
| 9 | 3 | 6 |
Затем такую же операцию редукции проводим по столбцам, для чего в каждом столбце находим минимальный элемент:
| 6 | 5 | 1 | |
| 7 | 5 | 4 | |
| 3 | 4 | ||
| 9 | 3 | 6 | |
После вычитания минимальных элементов получаем полностью редуцированную матрицу.
2. Методом проб и ошибок проводим поиск допустимого решения, для которого все назначения имеют нулевую стоимость.
Фиксируем нулевое значение в клетке (1, 1). Другие нули в строке 1 и столбце 1 вычеркиваем.
Фиксируем нулевое значение в клетке (2, 4). Другие нули в строке 2 и столбце 4 вычеркиваем.
Фиксируем нулевое значение в клетке (3, 2). Другие нули в строке 3 и столбце 2 вычеркиваем.
Фиксируем нулевое значение в клетке (4, 3). Другие нули в строке 4 и столбце 3 вычеркиваем.
В итоге получаем следующую матрицу:
| [0] | 6 | 5 | 1 |
| 7 | 5 | 4 | [0] |
| 3 | [0] | [-0-] | 4 |
| 9 | 3 | [0] | 6 |
Количество найденных нулей равно k = 4. В результате получаем эквивалентную матрицу Сэ:
| 6 | 5 | 1 | |
| 7 | 5 | 4 | |
| 3 | 4 | ||
| 9 | 3 | 6 |
4. Методом проб и ошибок определяем матрицу назначения Х, которая позволяет по аналогично расположенным элементам исходной матрицы (в квадратах) вычислить максимальное значение прибыли.
| [0] | 6 | 5 | 1 |
| 7 | 5 | 4 | [0] |
| 3 | [0] | [-0-] | 4 |
| 9 | 3 | [0] | 6 |
Cmax = 9 + 11 + 8 + 15 = 43
Таким образом, распределение продавцов по торговым точкам будет следующее:
1 продавец – торговая точка №1
2 продавец – торговая точка №4
3 продавец – торговая точка №2
4 продавец – торговая точка №3.
При таком назначении, максимальная эффективность составит 43.
Примеры
=AGGREGATE(4;2;A2:A9)
Возвращает максимальное значение в диапазоне A2:A9 =34, в то время как =MAX(A2:A9) возвращает ошибку.
=AGGREGATE(9;5;A5:C5)
Возвращает сумму в строке A5:C5 =29, даже если некоторые из столбцов скрыты.
=AGGREGATE(9;5;B2:B9)
Возвращает сумму в столбце B =115. Если какая-либо строка скрыта, то функция опустить её значение, например, если 7-ая строка скрыта, функция вернёт 95.
Если вам нужно применить функцию с диапазоном 3D, этот пример показывает, как это сделать.
=AGGREGATE(13;3;Sheet1.B2:B9:Sheet3.B2:B9)
Функция возвращает значения второго столбца сквозного (3D) диапазона листов 1:3 (который содержит те же данные) =8.
Вы можете использовать ссылку на ячейку или диапазон для любого аргумента в функции. В следующем примере показано, как это работает. Кроме того, это показывает, что вы можете использовать названия столбцов, чтобы задать массив.
