Эксель если целое число то
Предположим, что на вашем листе есть как целые, так и десятичные числа, и теперь вы хотите применить условное форматирование, чтобы выделить целые числа на вашем листе. Вы когда-нибудь пытались решить эту задачу самостоятельно? Сегодня я расскажу вам полезную формулу, чтобы завершить эту работу.
Вкладка Office позволяет редактировать и просматривать в Office с вкладками и значительно упрощает работу . Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Выделение целых чисел с помощью условного форматирования
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Чтобы выделить на листе только целые числа, вы можете использовать следующую формулу в условном форматировании, пожалуйста, сделайте следующее:
1. Выберите диапазон, в котором вы хотите выделить целые числа.
2. Нажмите Главная > Условное форматирование > Новое правило, см. снимок экрана:
3. В Новое правило форматирования диалоговое окно, нажмите Используйте формулу, чтобы определить, какие ячейки следует форматировать. вариант под Выберите тип правила: список, а затем введите эту формулу = ЕСЛИ (LEN (A1), MOD (A1,1) = 0, "") в Format значения, при которых эта формула верна: текстовое окно, (A1 - первая ячейка вашего выбора, вы можете изменить ее по своему усмотрению), см. снимок экрана:
4. Затем нажмите Формат , чтобы открыть Формат ячеек диалоговом окне и выберите нужный цвет в Заполнять вкладку, см. снимок экрана:
5, Затем нажмите OK > OK , чтобы закрыть диалоговые окна, и все числа в выбранном диапазоне будут выделены выбранным вами цветом.
Ноты:
1. Если вы хотите выделить только десятичные числа в диапазоне, вам следует применить эту формулу = МОД (A1,1)> 0 в Условное форматирование на шаге 3 выше.
2. Условное форматирование инструмент является динамической функцией, цвет выделения будет корректироваться с изменением вашего числа.
В этом посте представлены две формулы на основе функции СУММПРОИЗВ и ИЗМЕНИТЬ, которые помогут вам подсчитывать и суммировать только целые числа в диапазоне ячеек в Excel.
Как считать в Excel только целые числа
Как показано на скриншоте ниже, есть таблица продаж продуктов, чтобы подсчитать только целые числа в столбце Продажи, вы можете сделать следующее, чтобы достичь этого.
Общая формула
=SUMPRODUCT(--(MOD(range,1)=0))
аргументы
Диапазон: Диапазон ячеек, в которых вы хотите посчитать целые числа.Как пользоваться этой формулой?
1. Выберите пустую ячейку, чтобы разместить результат.
2. Введите в нее приведенную ниже формулу и нажмите Enter ключ для получения результата.
=SUMPRODUCT(--(MOD(E3:E17,1)=0))
Как работает эта формула?
=SUMPRODUCT(--(MOD(E3:E17,1)=0))
- 1) МОД (E3: E17,1): Здесь используйте функцию MOD для проверки целых чисел в E3: E17. Он возвращает остатки после каждого числа в диапазоне E3: E17 делится на 1: .
- 2) =0: Сравните каждое число в массиве с 0 и вернет массив ИСТИНА ЛОЖЬ, например: . В этом массиве значения ИСТИНА представляют собой целое число, а значения ЛОЖЬ представляют собой десятичное число.
- 3) - : Эти два знака минус преобразуют «ИСТИНА» в 1 и «ЛОЖЬ» в 0. Здесь вы получите новый массив как .
- 4) SUMPRODUCT : Функция СУММПРОИЗВ суммирует все числа в массиве и возвращает окончательный результат как 10.
Как суммировать только целые числа в Excel
Чтобы суммировать только целые числа в указанном диапазоне ячеек, вы можете сделать следующее.
В качестве примера возьмем приведенную выше таблицу продаж продуктов.
Общая формула
=SUMPRODUCT(--(MOD(range,1)=0)*range)
аргументы
Диапазон: Диапазон ячеек, по которым вы хотите суммировать целые числа.Как пользоваться этой формулой?
1. Выберите пустую ячейку, введите в нее приведенную ниже формулу и нажмите Enter ключ для получения результата.
=SUMPRODUCT(--(MOD(E3:E17,1)=0)*E3:E17)
Как работает эта формула?
=SUMPRODUCT(--(MOD(E3:E17,1)=0)*E3:E17)
- 1) МОД (E3: E17,1): Здесь используйте функцию MOD для проверки целых чисел в E3: E17. Он возвращает остатки после каждого числа в диапазоне E3: E17 делится на 1: .
- 2) =0: Сравните каждое число в массиве с 0 и вернет массив ИСТИНА ЛОЖЬ, например: . В этом массиве значения ИСТИНА представляют собой целое число, а значения ЛОЖЬ представляют собой десятичное число.
- 3) - : Эти два знака минус преобразуют «ИСТИНА» в 1 и «ЛОЖЬ» в 0. Здесь вы получите новый массив как .
- 4) SUMPRODUCT(*E3:E17): Здесь каждое число в массиве умножает числа в диапазоне E3: E17, чтобы получить следующий результат: СУММПРОИЗВ (0; 2028; 900; 3944; 2757; 1231; 0; 2313; 0; 3152; 1361; 0; 1980; 2579 ; 0), а затем функция СУММПРОИЗВ суммирует все числа в массиве и возвращает окончательный результат как 22245.
Связанные функции
Функция СУММПРОИЗВ в Excel
Функцию СУММПРОИЗВ в Excel можно использовать для умножения двух или более столбцов или массивов, а затем получения суммы произведений.
Функция Excel MOD
Функция Excel MOD возвращает остаток от деления числа на делитель. Результат имеет тот же знак, что и делитель.
Родственные формулы
Подсчитайте числа, которые начинаются с определенного числа
В этом руководстве представлена формула на основе функций СУММПРОИЗВ и ВЛЕВО для подсчета количества ячеек, содержащих числа, начинающиеся с определенного числа в Excel.
Подсчитайте несколько критериев с логикой НЕ в Excel
Эта статья покажет вам, как подсчитать количество ячеек с несколькими критериями с логикой НЕ в Excel.
Подсчет вхождений определенного текста во всей книге Excel
В этой статье будет продемонстрирована формула, основанная на функциях СУММПРОИЗВ, СЧЁТЕСЛИ и КОСВЕННО, для подсчета вхождений определенного текста во всей книге.
Подсчитайте числа, где n-я цифра равна заданному числу
В этом руководстве представлена формула, основанная на функциях СУММПРОИЗВ и СРЕДНЕЕ, для подсчета чисел, где n-я цифра равна заданному числу в Excel.
Лучшие инструменты для работы в офисе
Kutools for Excel - поможет вам выделиться из толпы
Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.
Функция ЕСЛИ — одна из самых популярных функций в Excel. Она позволяет выполнять логические сравнения значений и ожидаемых результатов.
Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.
Например, функция =ЕСЛИ(C2="Да";1;2) означает следующее: ЕСЛИ(С2="Да", то вернуть 1, в противном случае вернуть 2).
Функция ЕСЛИ, одна из логических функций, служит для возвращения разных значений в зависимости от того, соблюдается ли условие.
ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])
Имя аргумента
лог_выражение (обязательно)
Условие, которое нужно проверить.
значение_если_истина (обязательно)
Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.
значение_если_ложь (необязательно)
Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.
Простые примеры функции ЕСЛИ
В примере выше ячейка D2 содержит формулу: ЕСЛИ(C2 = Да, то вернуть 1, в противном случае вернуть 2)
В этом примере ячейка D2 содержит формулу: ЕСЛИ(C2 = 1, то вернуть текст "Да", в противном случае вернуть текст "Нет"). Как видите, функцию ЕСЛИ можно использовать для сравнения и текста, и значений. А еще с ее помощью можно оценивать ошибки. Вы можете не только проверять, равно ли одно значение другому, возвращая один результат, но и использовать математические операторы и выполнять дополнительные вычисления в зависимости от условий. Для выполнения нескольких сравнений можно использовать несколько вложенных функций ЕСЛИ.
=ЕСЛИ(C2>B2;"Превышение бюджета";"В пределах бюджета")
В примере выше функция ЕСЛИ в ячейке D2 означает: ЕСЛИ(C2 больше B2, то вернуть текст "Превышение бюджета", в противном случае вернуть текст "В пределах бюджета")
На рисунке выше мы возвращаем не текст, а результат математического вычисления. Формула в ячейке E2 означает: ЕСЛИ(значение "Фактические" больше значения "Плановые", то вычесть сумму "Плановые" из суммы "Фактические", в противном случае ничего не возвращать).
В этом примере формула в ячейке F7 означает: ЕСЛИ(E7 = "Да", то вычислить общую сумму в ячейке F5 и умножить на 8,25 %, в противном случае налога с продажи нет, поэтому вернуть 0)
Примечание: Если вы используете текст в формулах, заключайте его в кавычки (пример: "Текст"). Единственное исключение — слова ИСТИНА и ЛОЖЬ, которые Excel распознает автоматически.
Распространенные неполадки
0 (ноль) в ячейке
Не указан аргумент значение_если_истина или значение_если_ложь. Чтобы возвращать правильное значение, добавьте текст двух аргументов или значение ИСТИНА/ЛОЖЬ.
Как правило, это указывает на ошибку в формуле.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Проверка данных позволяет ограничить тип данных или значения, которые можно ввести в ячейку. Чаще всего она используется для создания раскрывающихся списков.
Проверьте, как это работает!
Выделите ячейки, для которых необходимо создать правило.
Выберите Данные > Проверка данных.
На вкладке Параметры в списке Тип данных выберите подходящий вариант:
Целое число, чтобы можно было ввести только целое число.
Десятичное число, чтобы можно было ввести только десятичное число.
Список, чтобы данные выбирались из раскрывающегося списка.
Дата, чтобы можно было ввести только дату.
Время, чтобы можно было ввести только время.
Длина текста, чтобы ограничить длину текста.
Другой, чтобы задать настраиваемую формулу.
В списке Значение выберите условие.
Задайте остальные обязательные значения с учетом параметров Тип данных и Значение.
Нажмите ОК.
Скачивание примеров
Ограничение ввода данных
Выделите ячейки, для которых нужно ограничить ввод данных.
На вкладке Данные щелкните Проверка данных > Проверка данных.
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.
В поле Тип данных выберите тип данных, который нужно разрешить, и заполните ограничивающие условия и значения.
Примечание: Поля, в которых вводятся ограничивающие значения, помечаются на основе выбранных вами данных и ограничивающих условий. Например, если выбран тип данных "Дата", вы сможете вводить ограничения в полях минимального и максимального значения с пометкой Начальная дата и Конечная дата.
Запрос для пользователей на ввод допустимых значений
Выделите ячейки, в которых для пользователей нужно отображать запрос на ввод допустимых данных.
На вкладке Данные щелкните Проверка данных > Проверка данных.
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.
На вкладке Подсказка по вводу установите флажок Отображать подсказку, если ячейка является текущей.
На вкладке Данные щелкните Проверка данных > Проверка данных.
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.
Выполните одно из следующих действий.
В контекстном меню Вид выберите
Требовать от пользователей исправления ошибки перед продолжением
Предупреждать пользователей о том, что данные недопустимы, и требовать от них выбора варианта Да или Нет, чтобы указать, нужно ли продолжать
Предупреждение
Добавление проверки данных в ячейку или диапазон ячеек
Примечание: Первые два действия, указанные в этом разделе, можно использовать для добавления любого типа проверки данных. Действия 3–7 относятся к созданию раскрывающегося списка.
Выделите одну или несколько ячеек, к которым нужно применить проверку.
На вкладке Данные в группе Работа с данными нажмите кнопку Проверка данных.
На вкладке Параметры в поле Разрешить выберите Список.
В поле Источник введите значения списка, разделенные запятыми. Например, введите Низкий,Средний,Высокий.
Убедитесь, что установлен флажок Список допустимых значений. В противном случае рядом с ячейкой не будет отображена стрелка раскрывающегося списка.
Чтобы указать, как обрабатывать пустые (нулевые) значения, установите или снимите флажок Игнорировать пустые ячейки.
После создания раскрывающегося списка убедитесь, что он работает так, как нужно. Например, можно проверить, достаточно ли ширины ячеек для отображения всех ваших записей.
Отмена проверки данных. Выделите ячейки, проверку которых вы хотите отменить, щелкните Данные > Проверка данных и в диалоговом окне проверки данных нажмите кнопки Очистить все и ОК.
В таблице перечислены другие типы проверки данных и указано, как применить их к данным на листе.
Разрешить вводить только целые числа из определенного диапазона
Выполните действия 1–2, указанные выше.
В списке Разрешить выберите значение Целое число.
В поле Данные выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Диапазон.
Введите минимальное, максимальное или определенное разрешенное значение.
Можно также ввести формулу, которая возвращает числовое значение.
Например, допустим, что вы проверяете значения в ячейке F1. Чтобы задать минимальный объем вычетов, равный значению этой ячейки, умноженному на 2, выберите пункт Больше или равно в поле Данные и введите формулу =2*F1 в поле Минимальное значение.
Разрешить вводить только десятичные числа из определенного диапазона
Выполните действия 1–2, указанные выше.
В поле Разрешить выберите значение Десятичный.
В поле Данные выберите необходимый тип ограничения. Например, для задания верхнего и нижнего пределов выберите ограничение Диапазон.
Введите минимальное, максимальное или определенное разрешенное значение.
Можно также ввести формулу, которая возвращает числовое значение. Например, для задания максимального значения комиссионных и премиальных в размере 6% от заработной платы продавца в ячейке E1 выберите пункт Меньше или равно в поле Данные и введите формулу =E1*6% в поле Максимальное значение.
Примечание: Чтобы пользователи могли вводить проценты, например "20 %", в поле Разрешить выберите значение Десятичное число, в поле Данные задайте необходимый тип ограничения, введите минимальное, максимальное или определенное значение в виде десятичного числа, например 0,2, а затем отобразите ячейку проверки данных в виде процентного значения, выделив ее и нажав кнопку Процентный формат на вкладке Главная в группе Число.
Разрешить вводить только даты в заданном интервале времени
Выполните действия 1–2, указанные выше.
В поле Разрешить выберите значение Дата.
В поле Данные выберите необходимый тип ограничения. Например, для разрешения даты после определенного дня выберите ограничение Больше.
Введите начальную, конечную или определенную разрешенную дату.
Вы также можете ввести формулу, которая возвращает дату. Например, чтобы задать интервал времени между текущей датой и датой через 3 дня после текущей, выберите пункт Между в поле Данные, потом введите =СЕГОДНЯ() в поле Дата начала и затем введите =СЕГОДНЯ()+3 в поле Дата завершения.
Разрешить вводить только время в заданном интервале
Выполните действия 1–2, указанные выше.
В поле Разрешить выберите значение Время.
В поле Данные выберите необходимый тип ограничения. Например, для разрешения времени до определенного времени дня выберите ограничение меньше.
Укажите время начала, окончания или определенное время, которое необходимо разрешить. Если вы хотите ввести точное время, используйте формат чч:мм.
Например, если в ячейке E2 задано время начала (8:00), а в ячейке F2 — время окончания (17:00) и вы хотите ограничить собрания этим промежутком, выберите между в поле Данные, а затем введите =E2 в поле Время начала и =F2 в поле Время окончания.
Разрешить вводить только текст определенной длины
Выполните действия 1–2, указанные выше.
В поле Разрешить выберите значение Длина текста.
В поле Данные выберите необходимый тип ограничения. Например, для установки определенного количества знаков выберите ограничение Меньше или равно.
В данном случае нам нужно ограничить длину вводимого текста 25 символами, поэтому выберем меньше или равно в поле Данные и введем 25 в поле Максимальное значение.
Вычислять допустимое значение на основе содержимого другой ячейки
Выполните действия 1–2, указанные выше.
В поле Разрешить выберите необходимый тип данных.
В поле Данные выберите необходимый тип ограничения.
В поле или полях, расположенных под полем Данные, выберите ячейку, которую необходимо использовать для определения допустимых значений.
Например, чтобы допустить ввод сведений для счета только тогда, когда итог не превышает бюджет в ячейке E1, выберите значение Число десятичных знаков в списке Разрешить, ограничение "Меньше или равно" в списке "Данные", а в поле Максимальное значение введите >= =E1.
В примерах ниже при создании формул с условиями используется настраиваемый вариант. В этом случае содержимое поля "Данные" не играет роли.
Представленные в этой статье снимки экрана созданы в Excel 2016, но функции аналогичны Excel в Интернете.
Введите формулу
Значение в ячейке, содержащей код продукта (C2), всегда начинается со стандартного префикса "ID-" и имеет длину не менее 10 (более 9) знаков.
Ячейка с наименованием продукта (D2) содержала только текст.
Значение в ячейке, содержащей чью-то дату рождения (B6), было больше числа лет, указанного в ячейке B4.
=ЕСЛИ(B6<=(СЕГОДНЯ()-(365*B4));TRUE,FALSE)
Все данные в диапазоне ячеек A2:A10 содержали уникальные значения.
=СЧЁТЕСЛИ($A$2:$A$10;A2)=1
Примечание: Необходимо сначала ввести формулу проверки данных в ячейку A2, а затем скопировать эту ячейку в ячейки A3:A10 так, чтобы второй аргумент СЧЁТЕСЛИ соответствовал текущей ячейке. Часть A2)=1 изменится на A3)=1, A4)=1 и т. д.
Читайте также: