Управление приоритетом правил условного форматирования. Если в excel

Подсчет количества вхождений значения - Служба поддержки Office

Примечание:  Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

Предположим, что нужно определить, сколько раз встречается конкретный текст или число в диапазоне ячеек. Например:

• если диапазон, например A2:D20, содержит числовые значения 5, 6, 7 и 6, то число 6 встречается два раза;

• если столбец содержит значения «Шашков», «Туманов», «Туманов» и «Туманов», то значение «Туманов» встречается три раза.

Примечание: Снимки экрана в этой статье сделаны в программе Excel 2016. В зависимости от версии Excel экран может выглядеть по-другому, но функциональность будет такой же.

Подсчитать количество вхождений значения можно несколькими способами.

Подсчет количества вхождений отдельного значения с помощью функции СЧЁТЕСЛИ

Используйте функцию СЧЁТЕСЛИ, чтобы узнать, сколько раз встречается определенное значение в диапазоне ячеек.

Дополнительные сведения см. в статье Функция СЧЁТЕСЛИ.

Подсчет количества вхождений на основе нескольких критериев с помощью функции СЧЁТЕСЛИМН

Функция СЧЁТЕСЛИМН аналогична функции СЧЁТЕСЛИ с одним важным исключением: СЧЁТЕСЛИМН позволяет применить критерии к ячейкам в нескольких диапазонах и подсчитывает число соответствий каждому критерию. С функцией СЧЁТЕСЛИМН можно использовать до 127 пар диапазонов и критериев.

Синтаксис функции СЧЁТЕСЛИМН имеет следующий вид:

СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)

См. пример ниже.

Дополнительные сведения об использовании этой функции для подсчета вхождений в нескольких диапазонах и с несколькими условиями см. в статье Функция СЧЁТЕСЛИМН.

Подсчет количества вхождений на основе условий с помощью функций СЧЁТ и ЕСЛИ

Предположим, что нужно определить количество продавцов, продавших определенный товар в указанном регионе, или количество продаж свыше определенной суммы, сделанных конкретным продавцом. Для этого можно воспользоваться функциями ЕСЛИ и СЧЁТ, т. е. сначала функция ЕСЛИ используется для проверки условия, а затем, если функция ЕСЛИ возвращает значение ИСТИНА, применяется функция СЧЁТ для подсчета ячеек.

Примечания: 

• Формулы в этом примере должны быть введены как формулы массива. Если эта книга открыта в приложении Excel для Windows или Excel 2016 для Mac и вы хотите изменить формулу или создать похожую, нажмите клавишу F2, а затем клавиши CTRL+SHIFT+ВВОД, чтобы формула возвращала нужные результаты. В более ранних версиях Excel для Mac используйте клавиши

  +SHIFT+ВВОД.

• Чтобы эти примеры формул работали, вторым аргументом функции ЕСЛИ должно быть число.

Дополнительные сведения об этих функциях см. в статьях Функция СЧЁТ и Функция ЕСЛИ.

Подсчет количества вхождений нескольких текстовых и числовых значений с помощью функций СУММ и ЕСЛИ

В следующих примерах функции ЕСЛИ и СУММ используются вместе. Функция ЕСЛИ сначала проверяет значения в определенных ячейках, а затем, если возвращается значение ИСТИНА, функция СУММ складывает значения, удовлетворяющие условию.

Пример 1

Функция выше означает, что если диапазон C2:C7 содержит значения Шашков и Туманов, то функция СУММ должна отобразить сумму записей, в которых выполняется условие. Формула найдет в данном диапазоне три записи для "Шашков" и одну для "Туманов" и отобразит 4.

Пример 2

Функция выше означает, что если ячейка D2:D7 содержит значения меньше 9 000 ₽ или больше 19 000 ₽, то функция СУММ должна отобразить сумму всех записей, в которых выполняется условие. Формула найдет две записи D3 и D5 со значениями меньше 9 000 ₽, а затем D4 и D6 со значениями больше 19 000 ₽ и отобразит 4.

Пример 3

Функция выше означает, что если диапазон D2:D7 содержит счета для записи "Шашков" на сумму меньше 9 000 ₽, то функция СУММ должна отобразить сумму записей, в которых выполняется условие. Формула найдет ячейку C6, которая соответствует условию, и отобразит 1.

Примечание: Формулы в этом примере должны быть введены как формулы массива. Это означает, что сначала нужно нажать клавишу F2, а затем клавиши CTRL+SHIFT+ВВОД. В более ранних версиях Excel для Mac используйте клавиши +SHIFT+ВВОД.

Подсчет количества вхождений нескольких значений, с помощью сводной таблицы

Сводную таблицу можно использовать для отображения итогов и подсчет количества уникальных значений. Сводная таблица образом интерактивных можно вычислять итоговые значения для больших объемов данных. Сводную таблицу можно использовать для развертывание и свертывание уровней данных точных результатов и детализация до итоговых данных по интересующим интересных интересующие вас сведения. Кроме того можно переместить строк в столбцы или столбцов в строки («сведение»), чтобы увидеть количество, как много раз значение возникает в сводной таблице. Рассмотрим пример таблица продаж, где можно подсчитать количество продаж значений существуют для гольфа и Теннис для определенных кварталам.

1. Введите данные в электронную таблицу Excel.

   

2. Выделите диапазон A2:C8

3. Выберите Вставка > Сводная таблица.

4. В диалоговом окне "Создание сводной таблицы" установите переключатель Выбрать таблицу или диапазон, а затем — На новый лист и нажмите кнопку ОК.

   Пустая сводная таблица будет создана на новом листе.

5. В области "Поля сводной таблицы" выполните одно из указанных ниже действий.

   1. Перетащите элемент Спорт в область Строки.

   2. Перетащите элемент Квартал в область Столбцы.

   3. Перетащите элемент Продажи в область Значения.

   4. Повторите третье действие.

      Имя поля Сумма_продаж_2 отобразится и в области "Сводная таблица", и в области "Значения".

      На этом этапе область "Поля сводной таблицы" будет выглядеть так:

      

   5. В области Значения щелкните стрелку раскрывающегося списка рядом с полем Сумма_продаж_2 и выберите пункт Параметры поля значений.

   6. В диалоговом окне Параметры поля значений выполните указанные ниже действия.

      1. На вкладке Операция выберите пункт Количество.

      2. В поле Пользовательское имя измените имя на Количество.

         

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

   Сводная таблица отобразит количество записей для разделов "Гольф" и "Теннис" за кварталы 3 и 4, а также показатели продаж.

   

К началу страницы

У вас есть вопрос об Excel?

Задайте его на форуме сообщества, посвященного Excel

Помогите нам улучшить Excel

У вас есть предложения по улучшению следующей версии Excel? Если да, ознакомьтесь с темами на портале пользовательских предложений для Excel.

См. также

Общие сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск и исправление ошибок в формулах

Сочетания клавиш и функциональные клавиши в Excel

Статистические функции (справка)

Математические и тригонометрические функции (справочник)

Функции Excel (по алфавиту)

Функции Excel (по категориям)

support.office.com

Проверка ячейки на наличие в ней текста (без учета регистра)

Примечание:  Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки. Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы. Для удобства также приводим ссылку на оригинал (на английском языке) .

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

Для поиска текста можно также использовать фильтр. Дополнительные сведения см. в статье Фильтрация данных.

Поиск ячеек, содержащих текст

Выполните следующие действия, чтобы найти ячейки, содержащие определенный текст.

1. Выделите диапазон ячеек, среди которых требуется осуществить поиск.

   Чтобы выполнить поиск по всему листу, щелкните любую ячейку.

2. На вкладке Главная в группе Редактирование нажмите кнопку Найти и выделить и нажмите кнопку Найти.

   

3. В поле Найти введите текст — или номера —, вам нужно найти. Или выберите из раскрывающегося списка Найти последнего поиска.

   Примечание: В условиях поиска можно использовать подстановочные знаки.

4. Чтобы задать формат для поиска, нажмите кнопку Формат и внесите нужные изменения во всплывающем окне Найти формат.

5. Кнопка Параметры служит для задания более подробных условий поиска. Например, можно найти все ячейки, содержащие данных определенного типа, такого как формулы.

   Для поиска на текущем листе или во всей книге можно выбрать в поле Искать вариант Лист или Книга.

6. Нажмите кнопку Найти все или Найти далее.

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

Примечание: Чтобы прекратить процесс поиска, нажмите клавишу ESC.

Проверка ячейки на наличие в ней любого текста

Для выполнения этой задачи используется функция ЕТЕКСТ .

Проверка соответствия содержимого ячейки определенному тексту

Для возвращения результатов для условия, которое можно указать с помощью функции Если .

Проверка соответствия части ячейки определенному тексту

Для выполнения этой задачи используются функции Если, Поиски ЕЧИСЛО .

Примечание: Функция поиска не учитывается регистр.

support.office.com

Управление приоритетом правил условного форматирования

Создание, редактирование, удаление и просмотр всех правил условного форматирования в книге выполняются в диалоговом окне Диспетчер правил условного форматирования. (Для этого на вкладке Главная выберите команду Условное форматирование и выберите пункт Управление правилами.)

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

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

В данном примере ячейки с идентификационными номерами сотрудников, у которых даты сертификации истекают через 60 дней, отформатированы желтым, а идентификационные номера сотрудников с истекшим сроком сертификации отформатированы красным (см. рис. ниже).

Первое правило (которое задает красный цвет фона ячейки, если значение — "истина") проверяет значение даты в столбце B напротив текущей даты (вычисленной с помощью функции СЕГОДНЯ в формуле). Формула для этого правила назначается первому значению в столбце B — B2 и выглядит так: =B2<СЕГОДНЯ(). Эта формула проверяет ячейки в столбце B (ячейки B2:B15). Если формула для какой-либо ячейки в столбце B оценивается как "истина", ее соответствующая ячейка в столбце A (например, A5 соответствует B5, а A11 — B11) форматируется красным цветом фона. После оценивания с помощью этого первого правила всех ячеек, указанных в группе Применяется к, проверяется второе правило. Данная формула проверяет, отстают ли значения в столбце B на 60 дней от текущей даты (предположим, сегодняшняя дата — 11.08.2010 г.). Ячейка в столбце B4 "4.10.2010" отстает от сегодняшней даты более чем на 60 дней, поэтому оценивается как "истина" и форматируется желтым цветом фона. Формула для данного правила выглядит так: =B2<СЕГОДНЯ()+60. Все ячейки, сначала отформатированные красным по правилу с наивысшим приоритетом в списке, остаются без изменений.

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

support.office.com

Ввод данных вручную в ячейки листа

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

Изменение направления клавиши ВВОД

Если использовать клавишу TAB для ввода данных в нескольких ячейках строки, а затем нажать клавишу ВВОД в конце этой строки, по умолчанию курсор переместится к началу следующей строки.

Нажатие клавиши ВВОД перемещает курсор на одну ячейку вниз, а нажатие клавиши TAB — на одну ячейку вправо. Направление перемещения для клавиши TAB изменить нельзя, но для клавиши ВВОД — можно. Изменение этого параметра влияет на весь лист, все другие открытые листы и книги, а также новые книги.

1. На вкладке Файл нажмите кнопку Параметры.

   В Excel 2007 только: нажмите Кнопку Microsoft Office , а затем щелкните Параметры Excel.

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

Изменение ширины столбца

Иногда в ячейке может отображаться строка вида #####. Это может происходить, если ячейка содержит число или дату, а ширина столбца не позволяет отобразить все необходимые символы. Допустим, например, что столбец с форматом даты "дд.мм.гггг" содержит дату 31.12.2015. Однако ширины столбца хватает только для показа шести символов. В ячейке будет отображена строка #####. Чтобы увидеть все содержимое ячейки с текущим форматом, необходимо увеличить ширину столбца.

1. Щелкните ячейку, для которой требуется изменить ширину столбца.

2. На вкладке Главная в группе Ячейки нажмите кнопку Формат.

   

3. Выделите пункт Размер ячейки и выполните одно из следующих действий.

   • Чтобы подогнать размер ячейки под текст, выберите команду Автоподбор ширины столбца.

   • Чтобы увеличить ширину столбца, выберите команду Ширина и в поле Ширина столбца введите нужное значение.

Примечание: Также ширину столбца можно увеличить, изменив формат столбца или отдельной ячейки. Например, можно изменить формат даты, чтобы отображался только день и месяц (формат "дд.мм"), например 31.12, или представить число в экспоненциальном формате, например 4E+08.

Перенос текста в ячейке

Путем переноса текста в ячейке можно отобразить несколько строк текста. Перенос текста в ячейке не влияет на другие ячейки.

1. Щелкните ячейку, в которой требуется выполнить перенос текста.

2. На вкладке Главная в группе Выравнивание выберите пункт Переносить текст.

   

Примечание: Если текст представляет собой одно длинное слово, знаки не будут перенесены; чтобы был виден весь текст, можно расширить столбец или уменьшить размер шрифта. Если после включения переноса текста виден не весь текст, возможно, потребуется изменить высоту строки. На вкладке Главная в группе Ячейки нажмите кнопку Формат и выберите в разделе Размер ячейки команду Автоподбор высоты строки.

Дополнительные сведения о переносе текста см. в разделе Перенос текста в ячейке.

Изменение формата числа

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

После ввода чисел в ячейку можно изменить формат их отображения.

1. Выберите ячейку, в которой содержатся нужные числа.

2. На вкладке Главная в группе Число щелкните стрелку рядом с полем Числовой формат и выберите нужный формат.

   

   Чтобы выбрать формат из списка доступных, нажмите кнопку Дополнительные числовые форматы и выберите нужный формат из списка Числовые форматы.

Форматирование чисел в виде текста

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

1. Выберите пустую ячейку.

2. На вкладке Главная в группе Число щелкните стрелку рядом с полем Числовой формат и выберите пункт Текстовый.

   

3. Введите нужные числа в отформатированной ячейке.

   Числа, которые были введены перед применением к ячейкам текстового формата, необходимо ввести заново в отформатированные ячейки. Чтобы ускорить повторный ввод чисел в текстовом формате, нажмите клавишу F2, а затем — клавишу ВВОД.

support.office.com

---

• 
  Почему на компе нет звука
• 
  Как проверить совместимость материнской платы и видеокарты
• 
  Восстановление флешки после форматирования
• 
  Ноутбук характеристики
• 
  Восстановление после форматирования флешки
• 
  Как восстановить систему назад
• 
  Как виндовс 10 зайти в безопасный режим
• 
  Не запускается пк
• 
  Как установить виндовс через флешку
• 
  Как пользоваться эксель
• 
  Виндовс 10 как удалить защитник виндовс