Как ограничить максимальное значение ячейки в excel

Обновлено: 07.07.2024

= Мир MS Excel/Статьи об Excel

Приёмы работы с книгами, листами, диапазонами, ячейками [6]

Приёмы работы с формулами [13]

Настройки Excel [3]

Инструменты Excel [4]

Интеграция Excel с другими приложениями [4]

Форматирование [1]

Выпадающие списки [2]

Примечания [1]

Сводные таблицы [1]

Гиперссылки [1]

Excel и интернет [1]

Excel для Windows и Excel для Mac OS [2]

Исходные данные
Исходными данными может быть любой числовой набор данных, таких как горизонтальный или вертикальный диапазон, двумерный массив или даже не смежные диапазоны или отдельно заполненные ячейки. Для удобства я взял одномерный вертикальный массив А1:А5

Для решения этой задачи можно использовать как дополнительный столбец с промежуточной формулой, так и одну формулу массива

1. Решение с дополнительным столбцом
В В1 напишите формулу

и скопируйте её на диапазон В2:В5. Эта промежуточная формула в дополнительном столбце будет "убирать" из исходных данных нули, заменяя их на пустую строку ""

В С1 напишите формулу

Она и вернёт искомый результат - число 3

Если в диапазоне есть отрицательные числа, то вместо формулы

2. Решение формулой массива
Для решения этой задачи используйте такую формулу массива:

Как это работает: Вот эта часть формулы A1:A5<>0 формирует массив , после чего применяя функцию ЕСЛИ() мы преобразуем массив в следующий: . Так как функция МИН() игнорирует текстовые и логические значения, то в итоге получаем искомое - число 3

Если в диапазоне есть отрицательные числа, то вместо формулы

3. Решение простой формулой
Используйте такую формулу:

Как это работает: С помощью функции СЧЁТЕСЛИ мы подсчитываем количество нулей в диапазоне и прибавив к этому количеству единицу мы возвращаем первое наименьшее число более нуля с помощью функции НАИМЕНЬШИЙ

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

Ограничить ввод ячеек целыми числами или числами в заданном диапазоне

Вы можете установить числовые значения с определенными ограничениями, выполнив следующие действия:

1. Выберите диапазон, в котором вы будете устанавливать записи числовых значений, и нажмите кнопку Данные> Проверка данных. Смотрите скриншот:

2. В открывшемся диалоговом окне «Проверка данных» перейдите на вкладку «Настройка» и:

(1) Выберите Целое число элемент из Разрешить выпадающий список. См. Первый снимок экрана ниже:

(2) Выберите один элемент из Данные раскрывающийся список в соответствии с вашими потребностями. Смотрите второй снимок экрана выше:
(3) Введите конкретные числа для ограничения в максимальная/минимальный/Длина коробка в соответствии с вашими потребностями.

3. Нажмите OK кнопку.

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

Ограничьте ввод в ячейку любым числовым значением (целыми / десятичными числами) или запретите использование определенных символов

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

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!

Ограничьте ввод ячеек целыми или десятичными числами:

Запретить вводить буквы в указанном диапазоне:

Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now

Ограничить ввод ячейки списком записей текстовых значений

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

1. Предустановите все тексты, которые другие пользователи могут вводить в ячейки, говоря, что предварительно установите список имен в A2: A10 как показано на следующем снимке экрана:

2. Выберите диапазон, в котором вы установите ограничение для текстовых записей в ячейках, и нажмите кнопку Данные> Проверка данных.

Ограничить ввод ячейки списком записей текстовых значений

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

3. В диалоговом окне Проверка данных перейдите к настройка вкладка и:
(1) Выберите Список элемент из Разрешить раскрывающийся список;
(2) Проверьте Раскрывающийся список внутри ячейки вариант;
(3) Укажите предустановленный источник. В данном случае это список имен в A2: A10.

4. Нажмите OK кнопку.

Затем выберите одну ячейку в диапазоне с ограничением ввода текста, она добавляет стрелку помимо активной ячейки. Щелкните стрелку, появится раскрывающийся список со списком предустановленных имен. Вы можете выбрать одно имя, чтобы легко заполнить активную ячейку. Смотрите скриншот ниже:

Ограничьте ввод дубликатов в один столбец / список одним щелчком мыши

Иногда вам может потребоваться ввести уникальные значения в столбец или список. Вы можете сделать это одним щелчком мыши с помощью Kutools for Excel's Предотвратить дублирование утилита.

Выберите столбец или список, в котором вы ограничите ввод дубликатов, а затем щелкните Kutools > Предотвратить дублирование. С этого момента, когда в указанном столбце или списке появляются повторяющиеся записи, он немедленно предупредит вас:

Демо: разрешить или запретить ввод только определенных символов в Excel

Легко ограничить ввод ячеек целыми / десятичными числами только в Excel

Полнофункциональная бесплатная 30-дневная пробная версия!

Есть ли у вас таблицы с данными в Excel, размеры которых могут изменяться, т.е. количество строк (столбцов) может увеличиваться или уменьшаться в процессе работы? Если размеры таблицы «плавают», то придется постоянно мониторить этот момент и подправлять:

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

Все это в сумме не даст вам скучать ;)

Гораздо удобнее и правильнее будет создать динамический «резиновый» диапазон, который автоматически будет подстраиваться в размерах под реальное количество строк-столбцов данных. Чтобы реализовать такое, есть несколько способов.

Способ 1. Умная таблица

Выделите ваш диапазон ячеек и выберите на вкладке Главная – Форматировать как Таблицу (Home – Format as Table):

Если вам не нужен полосатый дизайн, который добавляется к таблице побочным эффектом, то его можно отключить на появившейся вкладке Конструктор (Design). Каждая созданная таким образом таблица получает имя, которое можно заменить на более удобное там же на вкладке Конструктор (Design) в поле Имя таблицы (Table Name) .

Теперь можно использовать динамические ссылки на нашу «умную таблицу»:

Такие ссылки замечательно работают в формулах, например:

=СУММ(Таблица1[Москва]) – вычисление суммы по столбцу «Москва»

=ВПР(F5;Таблица1;3;0) – поиск в таблице месяца из ячейки F5 и выдача питерской суммы по нему (что такое ВПР?)

Такие ссылки можно успешно использовать при создании сводных таблиц, выбрав на вкладке Вставка – Сводная таблица (Insert – Pivot Table) и введя имя умной таблицы в качестве источника данных:

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

При создании выпадающих списков прямые ссылки на элементы умной таблицы использовать нельзя, но можно легко обойти это ограничение с помощью тактической хитрости – использовать функцию ДВССЫЛ (INDIRECT) , которая превращает текст в ссылку:

Т.е. ссылка на умную таблицу в виде текстовой строки (в кавычках!) превращается в полноценную ссылку, а уж ее выпадающий список нормально воспринимает.

Способ 2. Динамический именованный диапазон

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

Задача: сделать динамический именованный диапазон, который ссылался бы на список городов и автоматически растягивался-сжимался в размерах при дописывании новых городов либо их удалении.

Нам потребуются две встроенных функции Excel, имеющиеся в любой версии – ПОИКСПОЗ (MATCH) для определения последней ячейки диапазона и ИНДЕКС (INDEX) для создания динамической ссылки.

Ищем последнюю ячейку с помощью ПОИСКПОЗ

ПОИСКПОЗ(искомое_значение;диапазон;тип_сопоставления) – функция, которая ищет заданное значение в диапазоне (строке или столбце) и выдает порядковый номер ячейки, где оно было найдено. Например, формула ПОИСКПОЗ(“март”;A1:A5;0) выдаст в качестве результата число 4, т.к. слово «март» расположено в четвертой по счету ячейке в столбце A1:A5. Последний аргумент функции Тип_сопоставления = 0 означает, что мы ведем поиск точного соответствия. Если этот аргумент не указать, то функция переключится в режим поиска ближайшего наименьшего значения – это как раз и можно успешно использовать для нахождения последней занятой ячейки в нашем массиве.

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

Если в нашем массиве только числа, то можно в качестве искомого значения указать число, которое заведомо больше любого из имеющихся в таблице:

Для гарантии можно использовать число 9E+307 (9 умножить на 10 в 307 степени, т.е. 9 с 307 нулями) – максимальное число, с которым в принципе может работать Excel.

Если же в нашем столбце текстовые значения, то в качестве эквивалента максимально большого числа можно вставить конструкцию ПОВТОР(“я”;255) – текстовую строку, состоящую из 255 букв «я» - последней буквы алфавита. Поскольку при поиске Excel, фактически, сравнивает коды символов, то любой текст в нашей таблице будет технически «меньше» такой длинной «яяяяя….я» строки:

Формируем ссылку с помощью ИНДЕКС

Теперь, когда мы знаем позицию последнего непустого элемента в таблице, осталось сформировать ссылку на весь наш диапазон. Для этого используем функцию:

ИНДЕКС(диапазон; номер_строки; номер_столбца)

Она выдает содержимое ячейки из диапазона по номеру строки и столбца, т.е. например функция =ИНДЕКС(A1:D5;3;4) по нашей таблице с городами и месяцами из предыдущего способа выдаст 1240 – содержимое из 3-й строки и 4-го столбца, т.е. ячейки D3. Если столбец всего один, то его номер можно не указывать, т.е. формула ИНДЕКС(A2:A6;3) выдаст «Самару» на последнем скриншоте.

Причем есть один не совсем очевидный нюанс: если ИНДЕКС не просто введена в ячейку после знака =, как обычно, а используется как финальная часть ссылки на диапазон после двоеточия, то выдает она уже не содержимое ячейки, а ее адрес! Таким образом формула вида $A$2:ИНДЕКС($A$2:$A$100;3) даст на выходе уже ссылку на диапазон A2:A4.

И вот тут в дело вступает функция ПОИСКПОЗ, которую мы вставляем внутрь ИНДЕКС, чтобы динамически определить конец списка:

=$A$2:ИНДЕКС($A$2:$A$100; ПОИСКПОЗ(ПОВТОР("я";255) ;A2:A100))

Создаем именованный диапазон

Осталось упаковать все это в единое целое. Откройте вкладку Формулы (Formulas) и нажмите кнопку Диспетчер Имен (Name Manager) . В открывшемся окне нажмите кнопку Создать (New) , введите имя нашего диапазона и формулу в поле Диапазон (Reference) :

Осталось нажать на ОК и готовый диапазон можно использовать в любых формулах, выпадающих списках или диаграммах.

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

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

Также с помощью проверки данных реализуются выпадающие списки . Их удобно использовать, если у вас есть определенный набор значений, который может быть введен в диапазон (например, список марок автомобилей или ставок по кредиту).

Принцип создания выпадающих списков представлен в этой статье .

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

Немного полезной теории: вкладка "Проверка вводимых значений" (находится Лента->Данные->Проверка данных) сильно зависит от типа данных, на который у вас будет действовать ограничение:

Окно поддерживает следующие типы данных:

Любое значение - по сути при выборе этого пункта вы удаляете все предыдущие ограничения с этого диапазона;
Целое число - в ячейку должно быть введено целое число, которое можно ограничить диапазоном, например, целое число меньше или равное 80;
Действительное число - числа, которые могут быть записаны в виде конечной или бесконечной десятичной дроби. Например, 5; 47; -5,36; 3/7 и прочие;
Список - на основе этого типа создаются выпадающие списки;
Дата - в ячейку должна быть введена дата, можно также определить допустимый диапазон дат;
Время - аналогично типу дата, вводятся значения времени;
Длина текста - с помощью этого типа ограничивается длинна текста, которая может быть введена в ячейку.
Другой - в этом случае у вас появляется возможность ввести в поле формулу, при расчете которой если итог - Истина, то значение вводится в ячейку, если Ложь - выдает ошибку. Это на мой взгляд самый интересный тип данных, который есть в этом списке. Он значительно расширяет возможности этого инструмента.

Помимо типов данных в диалоговом окне "Проверка вводимых значений" есть такие кнопки:

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

Примеры ограничений при проверке данных:

1. ввод в ячейку только текстовых значений . Обеспечивается функцией =ЕТЕКСТ () . После ввода ограничения, в диапазон можно будет вводить только текст:

Подсчет максимального и минимального значения выполняется известными функциями МАКС и МИН. Бывает, что вычисления нужно произвести по группам или в зависимости от условия, как в СУММЕСЛИ.

Долгое время в Excel не было аналога СУММЕСЛИ или СРЗНАЧЕСЛИ для расчета максимального и минимального значения, поэтому использовали формулу массивов.

Пусть имеются данные

Нужно подсчитать максимальное значение в указанной группе. Название группы (критерий) введем в отдельную ячейку (D2). Пусть для начала это будет группа Б. Рядом введем следующую формулу:

Это формула массивов, поэтому ввести ее нужно комбинацией Ctrl + Shift + Enter.

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

Как это работает? Очень просто. Первым делом нужно указать диапазон, который будет использоваться в качестве аргумента функции МАКС, то есть только те ячейки, которые соответствуют указанной группе. Так как мы заранее позаботились об удобстве использования функции, то название группы указали не внутри формулы, а в отдельной ячейке (гораздо легче менять группу). Тогда формула для нужного диапазона выглядит так.

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

На следующем этапе укажем функцию МАКС, аргументом которой выступает полученный выше массив. Excel воспринимает примерно так.

Видно, что максимальное значение внутри массива равно 31. Его и мы и увидим в ячейке с формулой. Нужно только не забыть итоговую функцию ввести комбинацией клавиш Ctrl + Shift + Enter, иначе ничего не получится. В строке формул формула массива отображается внутри фигурных скобок. Добавляются сами, специально дорисовывать не нужно.

Если функцию МАКС заменить на МИН, то по указанному условию (названию группы) будет выдаваться минимальное значение.

Функции Excel 2016 МАКСЕСЛИ (MAXIFS) и МИНЕСЛИ (MINIFS)

Есть ряд значений, каждое из которых входит в некоторую группу. Нужно рассчитать максимальное значение по группе А. Используем формулу МАКСЕСЛИ.

Все очень просто. Как и у СУММЕСЛИМН вначале указываем диапазон, где находится искомое максимальное значение (колонка В), затем диапазон с критериями (колонка А) и далее сам критерий (в ячейке D2). Можно указать сразу несколько условий. Таким же способом легко рассчитать минимальное значение по условию. Найдем, к примеру, минимум внутри группы Б.

Ниже показан ролик, как рассчитать максимальное и минимальное значение по условию.

Читайте также: