Как сделать выборку в excel по цвету?

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

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

Для примера возьмем историю взаиморасчетов с контрагентами, как показано на рисунке:

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

Перед тем как выбрать уникальные значения в Excel, подготовим данные для выпадающего списка:

1. Выделите первый столбец таблицы A1:A19.
2. Выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Дополнительно».



3. В появившемся окне «Расширенный фильтр» включите «скопировать результат в другое место», а в поле «Поместить результат в диапазон:» укажите $F$1.



4. Отметьте галочкой пункт «Только уникальные записи» и нажмите ОК.

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



Теперь нам необходимо немного модифицировать нашу исходную таблицу. Выделите первые 2 строки и выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Вставить» или нажмите комбинацию горячих клавиш CTRL+SHIFT+=.

У нас добавилось 2 пустые строки. Теперь в ячейку A1 введите значение «Клиент:».

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

Перед тем как выбрать уникальные значения из списка сделайте следующее:

1. Перейдите в ячейку B1 и выберите инструмент «ДАННЫЕ»-«Работа с данными»-«Проверка данных».



2. На вкладке «Параметры» в разделе «Условие проверки» из выпадающего списка «Тип данных:» выберите значение «Список».



3. В поле ввода «Источник:» введите =$F$4:$F$8 и нажмите ОК.

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

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

Выборка ячеек из таблицы по условию в Excel:

1. Выделите табличную часть исходной таблицы взаиморасчетов A4:D21 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек».




2. Чтобы выбрать уникальные значения из столбца, в поле ввода введите формулу: =$A4=$B$1 и нажмите на кнопку «Формат», чтобы выделить одинаковые ячейки цветом. Например, зеленым. И нажмите ОК на всех открытых окнах.

Готово!

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

Скачать пример выборки из списка с условным форматированием.

Принцип действия автоматической подсветки строк по критерию запроса очень прост. Каждое значение в столбце A сравнивается со значением в ячейке B1. Это позволяет найти уникальные значения в таблице Excel.

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

Чтобы формат присваивался для целой строки, а не только ячейке в столбце A, мы используем смешанную ссылку в формуле =$A4.

Источник: https://exceltable.com/formatirovanie/kak-sdelat-vyborku-v-excel

Как в Excel выделить ячейку цветом при определенном условии

Не все фирмы покупают специальные программы для ведения дел. Многие пользуются MS Excel, ведь эта хо…

Не все фирмы покупают специальные программы для ведения дел. Многие пользуются MS Excel, ведь эта хорошо приспособлена для больших информационных баз. Практика показала, что дальше заполнения таблиц доходит редко.

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

Где находится условное форматирование

Как в экселе менять цвет ячейки в зависимости от значения – да очень просто и быстро. Для выделения ячеек цветом предусмотрена специальная функция «Условное форматирование», находящаяся на вкладке «Главная»:

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

Правила выделения ячеек

С помощью этого набора инструментов делают следующие выборки:

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

Посмотрите, как ищется выбранный текст: в первом поле задается условие, а во втором указывают, каким образом выделить полученный результат. Обратите внимание, выбрать можно цвет фона и текста из предложенных в списке. Если хочется применить иные оттенки – сделать это можно перейдя в «Пользовательский формат». Аналогичным образом реализуются все «Правила выделения ячеек».

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

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

Правила отбора первых и последних значений

Рассмотрим вторую группу функций «Правила отбора первых и последних значений». В ней вы сможете:

• выделить цветом первое или последнее N-ое количество ячеек;
• применить форматирование к заданному проценту ячеек;
• выделить ячейки, содержащие значение выше или ниже среднего в массиве;
• во вкладке «Другие правила» задать необходимый функционал.

Гистограммы

Если заливка ячейки цветом вас не устраивает – применяйте инструмент «Гистограмма». Предлагаемая окраска легче воспринимается на глаз в большом объеме информации, функциональные правила подстраиваются под требования пользователя.

Цветовые шкалы

Этот инструмент быстро формирует градиентную заливку показателей по выбору от большего к меньшему или наоборот. При работе с ним устанавливаются необходимые процентные отношения, либо текстовые значения. Предусмотрены готовые образцы градиента, но пользовательский подход опять же реализуется в «Других правилах».

Наборы значков

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

Создание, удаление и управление правилами

Функция «Создать правило» полностью дублирует «Другие правила» из перечисленных выше, создает выборку изначально по требованию пользователя.

С помощью вкладки «Удалить правило» созданные сценарии удаляются со всего листа, из выбранного диапазона значений, из таблицы.

Вызывает интерес инструмент «Управление правилами» – своеобразная история создания и изменения проведенных форматирований. Меняйте подборки, делайте правила неактивными, возвращайте обратно, чередуйте порядок применения. Для работы с большим объемом информации это очень удобно.

Отбор ячеек по датам

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

Для применения такого отбора нужны ячейки с установленным форматом «Дата». Для этого перед внесением информации выделите необходимый столбец, щелкните правой кнопкой мыши и в меню «Формат ячеек» найдите вкладку «Число».

Установите числовой формат «Дата» и выберите его тип по своему усмотрению.

Для отбора нужных дат применяем такую последовательность действий:

• выделяем столбцы с датами (в нашем случае за январь);
• находим инструмент «Условное форматирование»;
• в «Правилах выделения ячеек» выбираем пункт «Дата»;
• в правой части форматирования открываем выпадающее окно с правилами;
• выбираем подходящее правило (на примере выбраны даты за предыдущий месяц);
• в левом поле устанавливаем готовый цветовой подбор «Желтая заливка и темно-желтый текст»
• выборка окрасилась, жмем «ОК».

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

Выделение цветом столбца по условию

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

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

• выделяем столбец с январскими закупками;
• кликаем инструмент «Условное форматирование»;
• переходим в «Правила выделения ячеек»;
• пункт «Больше…»;
• в правой части форматирования устанавливаем сумму 100 000 рублей;
• в левом поле переходим на вкладку «Пользовательский формат» и выбираем синий цвет;
• необходимая выборка окрасилась в синий цвет, жмем «ОК».

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

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

Источник: https://FreeSoft.ru/blog/kak-v-excel-vydelit-yacheyku-tsvetom-pri-opredelennom-uslovii-primery-i-metody

Выборка данных в Microsoft Excel

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

Выполнение выборки

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

Способ 1: применение расширенного автофильтра

Наиболее простым способом произвести отбор является применение расширенного автофильтра. Рассмотрим, как это сделать на конкретном примере.

1. Выделяем область на листе, среди данных которой нужно произвести выборку. Во вкладке «Главная» щелкаем по кнопке «Сортировка и фильтр». Она размещается в блоке настроек «Редактирование». В открывшемся после этого списка выполняем щелчок по кнопке «Фильтр».
   

   Есть возможность поступить и по-другому. Для этого после выделения области на листе перемещаемся во вкладку «Данные». Щелкаем по кнопке «Фильтр», которая размещена на ленте в группе «Сортировка и фильтр».



2. После этого действия в шапке таблицы появляются пиктограммы для запуска фильтрования в виде перевернутых острием вниз небольших треугольников на правом краю ячеек. Кликаем по данному значку в заглавии того столбца, по которому желаем произвести выборку. В запустившемся меню переходим по пункту «Текстовые фильтры». Далее выбираем позицию «Настраиваемый фильтр…».



3. Активируется окно пользовательской фильтрации. В нем можно задать ограничение, по которому будет производиться отбор. В выпадающем списке для столбца содержащего ячейки числового формата, который мы используем для примера, можно выбрать одно из пяти видов условий:
   • равно;
   • не равно;
   • больше;
   • больше или равно;
   • меньше.

   Давайте в качестве примера зададим условие так, чтобы отобрать только значения, по которым сумма выручки превышает 10000 рублей. Устанавливаем переключатель в позицию «Больше». В правое поле вписываем значение «10000». Чтобы произвести выполнение действия, щелкаем по кнопке «OK».



4. Как видим, после фильтрации остались только строчки, в которых сумма выручки превышает 10000 рублей.



5. Но в этом же столбце мы можем добавить и второе условие. Для этого опять возвращаемся в окно пользовательской фильтрации. Как видим, в его нижней части есть ещё один переключатель условия и соответствующее ему поле для ввода. Давайте установим теперь верхнюю границу отбора в 15000 рублей. Для этого выставляем переключатель в позицию «Меньше», а в поле справа вписываем значение «15000».

   Кроме того, существует ещё переключатель условий. У него два положения «И» и «ИЛИ». По умолчанию он установлен в первом положении. Это означает, что в выборке останутся только строчки, которые удовлетворяют обоим ограничениям.

   Если он будет выставлен в положение «ИЛИ», то тогда останутся значения, которые подходят под любое из двух условий. В нашем случае нужно выставить переключатель в положение «И», то есть, оставить данную настройку по умолчанию.

   После того, как все значения введены, щелкаем по кнопке «OK».



6. Теперь в таблице остались только строчки, в которых сумма выручки не меньше 10000 рублей, но не превышает 15000 рублей.



7. Аналогично можно настраивать фильтры и в других столбцах. При этом имеется возможность сохранять также фильтрацию и по предыдущим условиям, которые были заданы в колонках. Итак, посмотрим, как производится отбор с помощью фильтра для ячеек в формате даты. Кликаем по значку фильтрации в соответствующем столбце. Последовательно кликаем по пунктам списка «Фильтр по дате» и «Настраиваемый фильтр».

Источник: https://lumpics.ru/how-to-make-a-sample-in-excel/

Фильтр и сортировка ячеек по цвету в Excel

• Рассмотрим основные способы фильтрации и сортировки данных по цвету (как по заливке ячейки, так и по заливке текста) в Excel.
• Для начала вспомним, в чем же польза от сортировки и фильтрации данных в Excel, и зачем она вообще нужна?

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

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

В общем случае в Excel можно сортировать по алфавиту (для текста), по возрастанию или убыванию (для чисел), однако давайте познакомимся с еще одним вариантом сортировки — по цвету, и рассмотрим 2 способа, позволяющие сортировать и применять фильтр к данным:

• Автофильтр и инструмент «Настраиваемая сортировка» (доступен начиная с версии Excel 2007);
• Применение пользовательских функций.

Стандартный фильтр и сортировка по цвету в Excel

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

Добавим фильтр к диапазону с таблицей (выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр или воспользуемся сочетанием клавиш Ctrl + Shift + L), далее щелкаем по стрелке в заголовке столбца и в выпадающем списке можем выбрать любой вариант сортировки или фильтрации:

С сортированием подобных проблем не возникает, для этого необходимо последовательно отсортировать данные по заданным цветам.

Например, если мы хотим, чтобы сначала в таблице были показаны ячейки с красной заливкой, а затем с синей, то на первом шаге сделаем сортировку по синей заливке (т.е. сортируем данные в обратном порядке — если в конечном итоге нужен порядок ячеек красный -> синий, то сортируем в порядке синий -> красный):

На втором шаге повторно произведем сортирование данных, но на этот раз выберем в качестве критерия сортировки красную заливку:

Аналогичного результата также можно добиться отсортировав данные с помощью инструмента Настраиваемая сортировка (также выбираем вкладку Главная -> Редактирование -> Сортировка и фильтр), где можно настроить различные дополнительные параметры и уровни сортировки:

Сортировка и фильтр по цвету с помощью функций

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

Функция цвета заливки ячейки на VBA

Для создания пользовательских функций перейдем в редактор Visual Basic (комбинация клавиш Alt + F11), создадим новый модуль и добавим туда код следующей функции:

Public Function ColorFill(MyCell As Range)
ColorFill = MyCell.Interior.ColorIndex
End Function

Public Function ColorFill(MyCell As Range)    ColorFill = MyCell.Interior.ColorIndex

Функция ColorFill в качестве значения возвращает числовой код цвета заливки ячейки, она очень схожа с функцией, которую мы использовали при подсчете количества и суммирования ячеек по цвету.

Вернемся в Excel и применим новую функцию ColorFill — либо непосредственно введем формулу в ячейку, либо вызовем ее с помощью мастера функций (выбрав из категории Определенные пользователем).
В дополнительном столбце прописываем код заливки ячейки:

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

Функция цвета текста ячейки на VBA

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

Public Function ColorFont(MyCell As Range)
ColorFont = MyCell.Font.ColorIndex
End Function

Public Function ColorFont(MyCell As Range)    ColorFont = MyCell.Font.ColorIndex

Функция ColorFont в качестве значения возвращает числовой код цвета шрифта ячейки и принцип ее применения аналогичен примеру рассмотренному выше.

Замечания

К сожалению, при работе с пользовательскими функциями ColorFill и ColorFont есть несколько подводных камней:

• Они не работают с ячейками, в которых заливка определяется условным форматированием;
• При изменении раскраски ячейки в Excel формулы автоматически не пересчитываются, в связи с этим пересчет нужно сделать самостоятельно (Shift + F9 для пересчета формул только на активном листе, F9 — для всей книги).

Удачи вам и до скорых встреч на страницах блога Tutorexcel.ru!

Источник: https://tutorexcel.ru/diapazony/filtr-i-sortirovka-yacheek-po-cvetu-v-excel/

Excel — выборка ячеек по цвету заливки

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

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

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

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

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

Прекрасное и готовое решение моей задачи я нашел на сайте http://www.excel-vba.ru/. Даже не одно, а целых два решения, под разные условия. Ниже привожу последовательность шагов, которые привели меня к успеху.

Сразу скажу, что изображения были сделаны в Excel 2007. В Excel 2010 все несколько по другому, но запутаться невозможно, если что.

Режим “Разработчик” в Excel

Первое, что нужно сделать — заставить Excel работать с пользовательскими функциями. Фактически, мы будем писать сценарий на языке VBA в Excel, но такая возможность по умолчанию отключена в этой программе. Включить ее можно следующим образом.

Переходим в “Пуск — Параметры Excel” и находим в левом списке пункт “Надстройки”:

Выбираем в основном окне строчку “Пакет анализа — VBA” и жмем кнопочку “Перейти” в самом низу окна. Откроется еще одно окошко со списком доступных под Excel расширений (надстроек). Снова выбираем в этом списке “Пакет анализа — VBA” и соглашаемся, что хотим установить его, нажав кнопку “ОК”:

Потребуется установочный диск с Microsoft Office на нем (или же подключение к Интернет) чтобы программа получила необходимые пакеты для инсталляции. Если установка прошла успешно, то в “Ленте” появиться пункт “Разработчик” (Excel 2010). Можно перейти в него через эту панель или же с помощью сочетания клавиш Alt + F11.

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

Вставка готовых функций в Excel VBA

Но есть готовые решения, которые я вставлю в виде кода с помощью меню “Insert — Module”. Просто берем отсюда код функций и вставляем в свой Excel. Затем сохраняем файл Excel с поддержкой VBA (макросов) и все готово для дальнейшей работы.

Вставленные функции появятся в списке формул таблицы:

Ниже представлен готовый код двух функций на VBA, написанных их автором Дмитрием Щербаковым. Первая функция с именем “CountByInteriorColor” выполняет подсчет количества ячеек по цвету заливки.

Вторая функция с именем “SumByInteriorColor” выполняет выборку ячеек по цвету заливки и суммирует все значения в этих ячейках.

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

• — диапазон с ячейками для подсчета
• — ячейка-образец с цветом заливки
• — или учитывает скрытые ячейки; , или опущен(по умолчанию) — скрытые ячейки не подсчитываются.

Функция подсчета количества ячеек

'—————————————————————————————
' Procedure : CountByInteriorColor
' Author : The_Prist(Щербаков Дмитрий)
' http://www.excel-vba.ru
' Purpose : Функция подсчета ячеек на основе цвета заливки.
' Аргументы:
' rRange — диапазон с ячейками для подсчета.
' rColorCell — ячейка-образец с цветом заливки.
' bSumHide — ИСТИНА или 1 учитывает скрытые ячейки.
' ЛОЖЬ, 0 или опущен(по умолчанию) — скрытые ячейки не подсчитываются.
'—————————————————————————————
Function CountByInteriorColor(rRange As Range, rColorCell As Range, Optional bSumHide As Boolean = False) Dim lColor As Long, rCell As Range, lCnt As Long, vVal lColor = rColorCell.Interior.Color For Each rCell In rRange If rCell.Interior.Color = lColor Then If rCell.EntireRow.Hidden Or rCell.EntireColumn.Hidden Then If bSumHide Then lCnt = lCnt + 1 Else lCnt = lCnt + 1 End If End If Next rCell CountByInteriorColor = lCnt
End Function

Синтаксис этой функции прост:

=CountByInteriorColor(D8:AG8;$E$65)

Функция подсчета суммы ячеек

'—————————————————————————————
' Procedure : SumByInteriorColor
' Author : The_Prist(Щербаков Дмитрий)
' http://www.excel-vba.ru
' Purpose : Функция суммирования ячеек на основе цвета заливки.
' Аргументы:
' rRange — диапазон с ячейками для суммирования.
' rColorCell — ячейка-образец с цветом заливки.
' bSumHide — ИСТИНА или 1 учитывает скрытые ячейки.
' ЛОЖЬ, 0 или опущен(по умолчанию) — скрытые ячейки не суммируются.
'—————————————————————————————
Function SumByInteriorColor(rRange As Range, rColorCell As Range, Optional bSumHide As Boolean = False) Dim lColor As Long, rCell As Range, dblSum As Double, vVal lColor = rColorCell.Interior.Color For Each rCell In rRange If rCell.Interior.Color = lColor Then vVal = rCell.Value If IsNumeric(vVal) Then If rCell.EntireRow.Hidden Or rCell.EntireColumn.Hidden Then If bSumHide Then dblSum = dblSum + vVal Else dblSum = dblSum + vVal End If End If End If Next rCell SumByInteriorColor = dblSum
End Function

Синтаксис этой функции следующий:

=SumByInteriorColor(D8:AG37;E63)

При вставке пользовательской функции “CountByInteriorColor” и “SumByInteriorColor” можно воспользоваться либо “Мастером функций”, либо произвести указание диапазона ячеек и ячейку-критерий вручную.

Описание рабочей формулы

Готовый пример работы функции “CountByInteriorColor” можно посмотреть на рисунке “Табель выходов с зелеными ячейками”. В нем подсчет отработанного времени производится по следующей формуле:

=((Сумма фактически отработанных часов) — (Норма часов выхода за месяц)) + ((Кол-во дней с переработкой)*4)

Фактически эта формула получается такой (смотри строку №13 на рисунке):

=(AH13-AI13) + (CountByInteriorColor(D13:AG13;$E$65)*4)

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

Первый «серьезный» метод в моей RxJs-копилке знаний. На самом деле все просто — этот метод получает на вход поток, обрабатывает каждый ev…… Continue reading

Published on September 02, 2019 Published on August 30, 2019

Источник: http://gearmobile.github.io/excel/excel-select-cell-by-color/

Запрос на выборку данных (формулы) в MS EXCEL

Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению стандартного Фильтра). Произведем отбор значений из исходной таблицы с помощью формул массива. В отличие от применения Фильтра (CTRL+SHIFT+L или ) отобранные строки будут помещены в отдельную таблицу.

В этой статье рассмотрим наиболее часто встречающиеся запросы, например: отбор строк таблицы, у которых значение из числового столбца попадает в заданный диапазон (интервал); отбор строк, у которых дата принаждежит определенному периоду; задачи с 2-мя текстовыми критериями и другие. Начнем с простых запросов.

1. Один числовой критерий (Выбрать те Товары, у которых цена выше минимальной)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — число).

Необходимо отобразить в отдельной таблице только те записи (строки) из Исходной таблицы, у которых цена выше 25.

Решить эту и последующие задачи можно легко с помощью стандартного фильтра. Для этого выделите заголовки Исходной таблицы и нажмите CTRL+SHIFT+L. Через выпадающий список у заголовка Цены выберите Числовые фильтры…, затем задайте необходимые условия фильтрации и нажмите ОК.

Будут отображены записи удовлетворяющие условиям отбора.

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

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

Теперь выделим диапазон D11:D19 (столбец Товар) и в Строке формул введем формулу массива:

=ИНДЕКС(A11:A19;НАИМЕНЬШИЙ(ЕСЛИ($E$6=$B$13:$B$21 гарантирует, что будут отобраны даты не позже заданной (включая). Условие $B$13:$B$21>0 необходимо, если в диапазоне дат имеются пустые ячейки. Знак * (умножение) используется для задания Условия И (все 3 критерия должны выполняться для строки одновременно).

Примечание. Случай, когда список несортирован, рассмотрен в статье Поиск ДАТЫ (ЧИСЛА) ближайшей к заданной, с условием в MS EXCEL. Несортированный список.

7. Один Текстовый критерий (Выбрать Товары определенного вида)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист Один критерий — Текст).

Задача решается аналогично Задачам 1 и 3. Более подробное решение см. в статье Поиск ТЕКСТовых значений в MS EXCEL с выводом их в отдельный список. Часть1. Обычный поиск.

8. Два Текстовых критерия (Выбрать Товары определенного вида, поставленные в заданный месяц)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (И)).

• Для отбора строк используется формула массива:
• =ИНДЕКС($A$11:$A$19;НАИМЕНЬШИЙ(ЕСЛИ(($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19);СТРОКА($A$11:$A$19)-СТРОКА($A$10);30);СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК($A$11:$A$19)))))
• Выражение ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) задает оба условия (Товар и Месяц).

Выражение СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК($A$11:$A$19))) формирует массив последовательных чисел {1:2:3:4:5:6:7:8:9}, т.е. номера строк в таблице.

9. Два Текстовых критерия (Выбрать Товары определенных видов)

Пусть имеется Исходная таблица с перечнем Товаров и Ценами (см. файл примера, лист 2 критерия — текст (ИЛИ)).

1. В отличие от Задачи 7 отберем строки с товарами 2-х видов (Условие ИЛИ).
2. Для отбора строк используется формула массива:
3. =ИНДЕКС(A$11:A$19;НАИБОЛЬШИЙ((($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19))*(СТРОКА($A$11:$A$19)-СТРОКА($A$10)); СЧЁТЕСЛИ($A$11:$A$19;$E$6)+СЧЁТЕСЛИ($A$11:$A$19;$E$7)-ЧСТРОК($A$11:A11)+1))

Условие ($E$6=$A$11:$A$19)+($E$7=$A$11:$A$19) гарантирует, что будут отобраны товары только заданных видов из желтых ячеек (Товар2 и Товар3). Знак + (сложение) используется для задания Условие ИЛИ (должен быть выполнен хотя бы 1 критерий).

Вышеуказанное выражение вернет массив {0:0:0:0:1:1:1:0:0}. Умножив его на выражение СТРОКА($A$11:$A$19)-СТРОКА($A$10), т.е. на массив последовательных чисел {1:2:3:4:5:6:7:8:9}, получим массив позиций (номеров строк таблицы), удовлетворяющих критериям. В нашем случае это будет массив {0:0:0:0:5:6:7:0:0}.

С помощью функции НАИБОЛЬШИЙ() выведем 3 значения из позиции 5 (строка 15 листа), 6 (16) и 7 (17), т.е. значения Товар2, Товар2 и Товар3.

;2) вернет число 6, НАИБОЛЬШИЙ(…;1) вернет число 7, а НАИБОЛЬШИЙ(…;0) и далее вернет ошибку, которую мы скроем условным форматированием.

И наконец, с помощью функции ИНДЕКС() последовательно выведем наши значения из соответствующих позиций: =ИНДЕКС(A$11:A$19;5) вернет Товар2, =ИНДЕКС(A$11:A$19;6) вернет Товар2, =ИНДЕКС(A$11:A$19;7) вернет Товар3.

10. Отбор значений с учетом повторов

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

• Наиболее популярные статьи из этого раздела:
• Отбор уникальных значений (убираем повторы из списка) в MS EXCEL
• Отбор уникальных значений с суммированием по соседнему столбцу в MS EXCEL
• Отбор повторяющихся значений в MS EXCEL
• Отбор уникальных значений из двух диапазонов в MS EXCEL
• Отбор уникальных СТРОК с помощью Расширенного фильтра в MS EXCEL
• В качестве примера приведем решения следующей задачи: Выбрать Товары, цена которых лежит в определенном диапазоне и повторяется заданное количество раз или более.
• В качестве исходной возьмем таблицу партий товаров.

Предположим, что нас интересует сколько и каких партий товаров поставлялось по цене от 1000р. до 2000р. (критерий 1). Причем, партий с одинаковой ценой должно быть минимум 3 (критерий 2).

1. Решением является формула массива:
2. =НАИМЕНЬШИЙ(СТРОКА($A$14:$A$27)*($C$14:$C$27>=$B$7)*($C$14:$C$27=$B$10);F14+($G$8-$G$9))
3. Эта формула возвращает номера строк, которые удовлетворяют обоим критериям.
4. Формула =СУММПРОИЗВ(($C$14:$C$27>=$B$7)*($C$14:$C$27=$B$10)) подсчитывает количество строк, которые удовлетворяют критериям.

В файле примера на листе «10.Критерий — колич-во повторов» настроено Условное форматирование, которое позволяет визуально определить строки удовлетворяющие критериям, а также скрыть ячейки, в которых формула массива возвращает ошибку #ЧИСЛО!

11. Используем значение критерия (Любой) или (Все)

В фильтре Сводных таблиц MS EXCEL используется значение (Все), чтобы вывести все значения столбца. Другими словами, в выпадающем списке значений критерия содержится особое значение, которое отменяет сам критерий (см. статью Отчеты в MS EXCEL, Отчет №3).

В файле примера на листе «11. Критерий Любой или (Все)» реализован данный вариант критерия.

Формула в этом случае должна содержать функцию ЕСЛИ(). Если выбрано значение (Все), то используется формула для вывода значений без учета данного критерия. Если выбрано любое другое значение, то критерий работает обычным образом.

• =ЕСЛИ($C$8=»(Все)»;НАИМЕНЬШИЙ((СТРОКА($B$13:$B$26)-СТРОКА($B$12))*($D$13:$D$26>=$D$8);F13+($G$6-$G$7));
• НАИМЕНЬШИЙ((СТРОКА($B$13:$B$26)-СТРОКА($B$12))*($D$13:$D$26>=$D$8)*($C$13:$C$26=$C$8);F13+($G$6-$G$7)))
• Остальная часть формулы аналогична рассмотренным выше.

Источник: https://excel2.ru/articles/zapros-na-vyborku-dannyh-formuly-v-ms-excel

Как сделать выборку в excel из списка

​Смотрите также​ и потом еще​ спасибо огромное.​: Насчет подъемки, я​ же приемщицы. Для​ водителей перепробовал, у​ «,» & z(i,​ CreateObject(«Scripting.Dictionary»)​: Добрый день Уважаемые​: почитайте про расширенный​Обратите внимание ниже на​ останутся только те​ примечание, но это​

​ его комнаты и​Каждый раз после ввода​

Выполнение выборки

​ вводимая формула будет​и​При работе с таблицами​ один, который заменяет​Лесник​ увидел в формуле​ лесозаг. бригад я​ всех только Д.1,​ 2)​

Способ 1: применение расширенного автофильтра

​On Error Resume​ форумчане.​ фильтр.​ рисунок, где в​ товары, которые удовлетворяют​

1. ​ не обязательно. Жмем​ наименование подразделения, к​ не забываем набирать​ выглядеть следующим образом:​​«Настраиваемый фильтр»​​ Excel довольно часто​​ пустые значения в​​: LAD, затащил запросом​ ссылку только на​​ уже сам в​​ оказывается просто совпадение​End If​ Next​​Пытаюсь два дня​​п.с. второе значение​

   ​ ячейку B3 была​ запросу.​ на кнопку «OK».​ которому он относится.​ сочетание клавиш​​=ИНДЕКС(A2:A29;НАИМЕНЬШИЙ(ЕСЛИ(15000​​.​​ приходится проводить отбор​​ ячейках на ноли​ всю базу по​​ книгу из Туры​​ другом файле объем​

2. ​ такое. Выборка на​Next​For Each a​ найти решение в​ поиска нигде не​ введена данная формула​Другой способ решения –​Переходим во вкладку «Данные»​Необходимо отобразить всех сотрудников​Ctrl+Shift+Enter​Естественно, в каждом конкретном​Снова запускается окно пользовательского​ в них по​ в диапазоне A2:R1001,​​ вывозке, появились ошибки,​​ поэтому и задал​​ расчитываю пользуясь этими​​ самом деле работает​
3. ​Range(«I2»).Resize(.Count, UBound(z, 2)).Value​ In MyList'Список искомых​ нэте, но пока​ нашел. (​ в массиве:​ использование формулы массива.​ программы Microsoft Excel.​ выбранного отдела.​.​ случае адрес ячеек​ автофильтра. Выполним отбор​ определенному критерию или​
   • ​ т.к без этого​
   • ​ например за январь​
   • ​ такой вопрос. В​
   • ​ же данными (для​
   • ​ значительно быстрее по​

   ​ = z​ значений​ никак…​Disel1977​Выборка соответственного значения с​ Соответствующие запросу строки​ Выделяем область таблицы,​​Это можно легко сделать​​Преимущество данного способа перед​ и диапазонов будет​​ результатов в таблице​​ по нескольким условиям.​ потом запрос не​ 2009 если выбрать​​ Качканаре даже если​​ этого там и​

4. ​ сравнению с формулами.​Range(«J2:J» & Range(«J»​If a.Rows.Hidden =​Своих знаний не​: Думал сделать формулами,​
5. ​ первым наименьшим числом:​ поместятся в отдельный​ где собираемся применять​ с помощью стандартного​ предыдущим заключается в​ свой. На данном​ с 4 по​ В программе сделать​ берет данные если​ Все, то в​ и один водитель​ вставлены доп. столбцы​ Спасибо большое!​ & Rows.Count).End(xlUp).Row).HorizontalAlignment =​ False Then DicSearch.Add​ хватает, по этому​ сначала отобрать по​​С такой формулой нам​​ отчет-таблицу.​ выпадающий список. Жмем​​ фильтра EXCEL. Выделите​​ том, что если​

   ​ примере можно сопоставить​ 6 мая 2016​ это можно различными​​ в каком то​​ Качканар итоговые цифры​​ количество рейсов и​​ для кубометров по​LAD​ xlRight​ CStr(a.Value), a.Value​ обращаюсь к Вам.

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

   Скорость выборки​End With​Next a​Есть таблица с​ из результата по​ значение относительно чисел.​​ рядом с исходной:​​ данных», расположенную на​

6. ​ нажмите​ границы выборки, то​ на иллюстрации и​ переключателе выбора условий,​ ряда инструментов. Давайте​ стоят пустые ячейки.​
7. ​ числам нет. За​ день. А как​ которые разгрузили сразу​ – не единственное​End Sub​For i =​ исходными данными (вкладка​ второму. Но никак​ Далее разберем принцип​ дублируем заголовки, количество​ Ленте.​CTRL+SHIFT+L​ совсем не нужно​ приспособить её для​ как видим, ещё​ выясним, как произвести​​ Где то выше​​ 2008 г. вроде​​ учитывается работа операторов,​​ на эстакаду), там​
8. ​ достоинство. Сводную таблицу​Barbos_TN​ 1 To MyRange.Rows.Count​ Invoices), нужно на​ не подберу формулу​ действия формулы и​ строк и столбцов.​Открывается окно проверки вводимых​. Через выпадающий список​ будет менять саму​ своих нужд.​​ больше вариантов, чем​​ выборку в Экселе,​ я уже спрашивал​​ такого нет. И​​ они – то​ у меня все​ можно построить, не​​: Всем привет в​​ 'Список найденных значений​ отдельном листе (Data)​​ какую можно было​​ пошагово проанализируем весь​ Новая таблица занимает​ значений. Во вкладке​​ у заголовка Отделы​​ формулу массива, что​Так как это формула​ для числового формата.​ используя разнообразные варианты.​​ об этом.) Этот​​ у операторов за​
9. ​ меняются?​ нормально получается, а​ имея понятия о​ этом чатике​If DicSearch.Exists(CStr(MyRange.Cells(i, SearchColumn).Value))​ при выборе одной​ бы использовать.​ порядок всех вычислений.​ диапазон Е1:G10.Теперь выделяем​ «Параметры» в поле​
10. ​ выберите нужный отдел​ само по себе​ массива, то для​ Выбираем позицию​Скачать последнюю версию​ диапазон A2:R1001 именован​ 2009 г. такая​Насчет >данные>проверка>список Вы​ вот по операторам​​ формулах и программировании.​​!​
11. ​ Then​ из позиций из​Vlad999​​ Е2:Е10 (столбец «Дата»)​ «Тип данных» выбираем​ и нажмите ОК.​
12. ​ довольно проблематично. Достаточно​ того, чтобы применить​«После или равно»​​ Excel​​ как ОбщаяВывозка. Потом​ же штука на​ абсолютно правы.​ и водителям прилично​В свою очередь​Я снова к​

    ​If ZnachColumn >​ списка (ячейка B1),​: как вариант сделать​Ключевую роль здесь играет​​ и вводим следующую​​ параметр «Список». В​​Будут отображены все сотрудники​​ в колонке условий​

13. ​ её в действии,​. В поле справа​Выборка данных состоит в​​ уже в Вашем​​ подъемке и разгрузке.​​Насчет запросов прав​​ тормозит при расчетах.​ я то же​ вам за помощью.​​ 0 Then V​​ что бы в​ доп столбец с​ функция ИНДЕКС. Ее​​ формулу: {}.​​ поле «Источник» ставим​ выбранного отдела.​ на листе поменять​​ нужно нажимать не​​ устанавливаем значение​ процедуре отбора из​ файле на листе​​LAD​​ nik. В своем​Вроде все расписал.​ извиняюсь. В предложенной​Нужен простой алгоритм​ = CStr(MyRange.Cells(i, ZnachColumn).Value)​ колонку «С» он​​ формулой =ЕСЛИ($C5=$C$1;МАКС($C$5:$C5)+1;»»)​​ номинальное задание –​Чтобы получилась формула массива,​ знак равно, и​Другим подходом является использование​ граничные числа на​​ кнопку​​«04.05.2016»​
14. ​ общего массива тех​ Вывозка я создаю​: Лесник, насчет пустых​ сообщении он указывает,​И еще вопрос​ книге сводная таблица​ для заполнения пустых​ Else V =​
15. ​ вертикально выводил список​тогда в таблице​ это выбирать из​ нажимаем сочетание клавиш​ сразу без пробелов​ трехуровневого Связанного списка​ те, которые нужны​Enter​​. В нижнем блоке​​ результатов, которые удовлетворяют​​ запрос из именованого​​ таблиц Вы правы,​ что для запроса​​ — можно ли​​ построена немного неправильно.​

    ​ ячеек.​ i​​ всех строчек с​​ формула =ИНДЕКС(диапазон;ПОИСКПОЗ(строка(A1);доп. столбец;0))​ исходной таблицы (указывается​ Ctrl + Shift​​ пишем имя списка,​​ с использованием элементов​​ пользователю. Результаты отбора​​, а сочетание клавиш​ устанавливаем переключатель в​​ заданным условиям, с​​ диапазона ОбщаяВывозка, который​

​ я не учел​ можно выставлять условия,​ создать макрос, который​ Прилагаю правильно построенную​Описать сложно, в​Dic.Add V, CStr(MyRange.Cells(i,​ этой позиции из​Disel1977​ в первом аргументе​

​ + Enter. В​​ которое присвоили ему​

Способ 2: применение формулы массива

​ управления формы, где​ тут же автоматически​Ctrl+Shift+Enter​ позицию​ последующим выводом их​ работает при открытии​ особенность версий Excel​

1. ​ а обновить запрос​ бы открыл файл,​ таблицу. Так же​ файле пример того,​ SearchColumn).Value)​ списка исходных данных,​
2. ​: Извините, но так​ – A6:A18) значения​ соседний столбец –​ выше. Жмем на​ из исходной таблицы​ изменятся.​. Делаем это.​«До или равно»​ на листе отдельным​ файла (когда Вам​ и кроме этого​ можно из макроса​ выполнил в нем​ добавлена подгонка ширины​

   ​ что нужно.​

   ​End If​ рядом подставлял все​ не прокатит. (​ соответственные определенным числам.​ «Товар» — вводим​ кнопку «OK».​ Сотрудники последовательно выбирая​В Экселе с помощью​Выделив второй столбец с​

3. ​. В правом поле​ списком или в​ отправлял, отключил автообновление​ не правильно формируется​ (программно) в любое​ 3 макроса, сохранил​​ столбцов.​​Голову сломал, но​​Next i​​ значения, находящиеся справа​
4. ​Пока воспользуюсь Автопоиском.​ ИНДЕКС работает с​ аналогичную формулу массива:​Выпадающий список готов. Теперь,​ Дирекцию и Отдел,​
   • ​ специальной формулы​
   • ​ датами и установив​​ вписываем значение​​ исходном диапазоне.​
5. ​ при открытии) В​ список лет. Это​ время. Если в​

   ​ этот документ и​

   ​При работе с​​ не смог сам​​With Workbooks.Add(xlWBATWorksheet).Worksheets(1)'вывод результатов​

   ​ от этих позиций.​ А там может​ учетом критериев определённых​ {}. Изменился только​ при нажатии на​

Источник: https://my-excel.ru/tablicy/kak-sdelat-vyborku-v-excel-iz-spiska.html