000818.ru

Финансовые и Бух. услуги
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как определить и определить индекс цвета фона ячеек в Excel?

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

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

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

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

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

Табель выходов с зелеными ячейками

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

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

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

Создать правило

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

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

После выбора пункта «Создать правило…» программа отобразит окно:

Окно создания правил

Там можно выбрать тип правила и настроить его описание (подробнее см. ниже).

Сумма по цвету в Excel

В Excel есть два способа обобщения данных по цвету:

  1. Использование формулы ИТОГО в Excel и фильтрация по функции цвета.
  2. Применение формулы GET.CELL путем определения имени на вкладке формул и применение формулы SUMMESLY в Excel для суммирования значений по цветовым кодам.

Обсудим подробно каждый из них —

# 1 — Сумма по цвету с использованием функции промежуточных итогов

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

Сумма по цвету в Excel

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

Сумма по цвету в примере Excel

  • Шаг 2: Теперь, чтобы получить сумму цветных ячеек в Excel, введите формулу ИТОГО ИТОГО под таблицей данных. Синтаксис формулы TOTAL TOTAL показан ниже.

Сумма по цвету в Excel Пример1

Для подведения итогов вводится следующая формула:

Читайте так же:
Как объединить две или более таблиц в одну на основе ключевых столбцов?

= ОБЩИЙ ПАРТИЭЛЬ (9 ; D2 : D13).

Здесь число 9 в аргументе function_num относится к объему функциональности, а аргумент reference указан как диапазон ячеек, которые нужно вычислить. Ниже приведен снимок экрана.

Сумма по цвету в Excel Пример 1-2

  • Шаг 3: Как видно на скриншоте выше, сумма в долларах была рассчитана для расчета сумм, выделенных светло-красным цветом на заднем плане. Примените фильтр к таблице данных, перейдя в раздел «Данные» и выбрав фильтр.

Сумма по цвету в Excel Пример 1-3

  • Шаг 4: Далее выберите цветовой фильтр и выберите светло-красный цвет клеток в разделе «Фильтр по цвету клеток». Ниже приведен снимок экрана, который лучше описывает фильтр.

Сумма по цвету в Excel Пример1-4

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

Сумма по цвету в Excel Пример1-5

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

# 2 — Суммирование по цвету с использованием функции Get.Cell

Второй метод объясняется для получения суммы цветных ячеек в Excel, как описано в примере ниже. Для лучшего понимания методологии обратитесь к таблице данных.

Сумма по цвету в Excel Пример 2

  • Шаг 1: Выделите список ячеек в столбце «Сумма в долларах», где вы хотите получить нужное количество цветных ячеек, как показано ниже.

Сумма по цвету в Excel Пример 2-1

  • Шаг 2: Как видно на скриншоте выше, в отличие от первого примера, здесь у нас несколько цветов. Поэтому мы используем формулу = GET.CELL, определяя ее в поле имени, а не непосредственно в Excel.
  • Шаг 3: Когда появится диалоговое окно «Определить имя», введите имя и формулу forget.CELL в поле «Ссылаться на», как показано на скриншоте ниже.

Сумма по цвету в Excel Пример2-4

Как видно на приведенном выше снимке экрана, имя, введенное для функции, — «CellColor», а формула = GET.CELL (38, «Пример 2! $ D2) должна быть введена в поле« Относится к ». В формуле число 38 относится к информации о коде ячейки, а второй аргумент — это номер ячейки, D2 относится к ссылочной ячейке. Теперь щелкните, ХОРОШО.

  • Шаг 4: Теперь введите имя функции CellColor в ячейку рядом с ячейкой с кодом цвета, который был определен в диалоговом окне, как это было описано в шаге 3.

Сумма по цвету в Excel Пример2-5

Как видно на скриншоте выше, была введена функция CellColor, которая возвращает код цвета для фона ячейки.

Пример 2-6

Аналогично, весь столбец перетаскивается с формулой.

  • Шаг 5: Теперь, чтобы получить сумму значений по цветам в Excel, мы введем формулу СУММЕСЛИ. Синтаксис формулы СУММЕСЛИ: —
Читайте так же:
Как объединить или объединить рабочие листы или книги в один рабочий лист?

формула Sumif

Пример2-9

Как видно из приведенного выше снимка экрана, в формулу СУММЕСЛИ вводятся следующие аргументы: —

  • Аргумент интервала вводится для диапазона ячеек E2: E13.
  • Критерии, целые значения которых необходимо получить, вводятся как G2.
  • Вводится диапазон ячеек D2: D13.

Формула SUMMESLY вычитается для всех номеров цветового кода, для которых необходимо добавить значения.

Диапазон означает группу соседних ячеек. Удерживая нажатой левую кнопку мыши, переместите курсор в форме белого широкого креста на нужный диапазон ячеек. Первая ячейка диапазона остается незатененной и доступна для ввода информации. Лучший способ выделить большой диапазон — выделить первую ячейку, удерживать клавишу Shift и выделить последнюю ячейку. При этом будет выделен весь диапазон между этими двумя ячейками. Чтобы выделить диапазон ячеек, можно ввести английскими буквами и цифрами адрес нужного диапазона в адресном окне строки формул, используя в качестве разделителя символ двоеточия, например A1:A10. После ввода адреса диапазона нажмите Enter. Все ячейки в строке или все ячейки в столбце можно выделить щелчком мыши по названию столбца или номеру строки. Для выделения всех ячеек на листе можно щелкнуть по нулевой ячейке (пересечение имен столбцов и номеров строк) или воспользоваться сочетанием клавиш Ctrl+A (сочетание клавиш Все). Активная ячейка в момент выделения остается незатененной, и в нее можно вводить данные. При нажатой клавише Ctrl можно выделить все соседние ячейки по отдельности.

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

Приведу пример небольшого столика.

Выделим диапазон A1:D18.

Затем создайте новое правило с помощью формулы

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

В результате получилась «полосатая» таблица по принципу “четный-нечетный”.

Объекты MS Excel

Свойство Range возвращает объект Range, определенный аргументами. Для записи свойства Range можно использовать два способа.

Объект.Диапазон(Cell1)

Второй способ object.Range(Cell1 [,Cell2])

  • Объект — ссылка на объект, например, электронную таблицу или диапазон ячеек. Ссылка необязательна. По умолчанию используется активный рабочий лист;
  • Cell1, Cell2 — аргументы для указания диапазона ячеек. Cell1 — указание является обязательным для обоих методов регистрации свойства Range.
Читайте так же:
Как найти перекрывающиеся диапазоны дат / времени в Excel?

Первая формула

Интервал ячеек любого размера может быть задан аргументом Cell1.

  • Вы можете использовать имена, определенные в таблице, или координаты ячеек, столбцов, строк или диапазонов.
  • Координаты даны в стиле A1.
  • Координаты и имена заключаются в инвертированные запятые.
  • Если заданы диапазоны, координаты верхнего левого и нижнего правого углов диапазона разделяются двоеточием.
  • Запятая используется для обозначения диапазонов, которые не связаны друг с другом.
  • Пробел используется для обозначения пересекающихся диапазонов.
Примеры записи оператора Range (1 способ)

ЗаписьВозвращаемый объект
ActiveSheet.Range(«A1: A10 «)интервал ячеек A1: A10 на активном листе
Range(«A:B»)столбцы A:B
Range(«налог»)интервал с именем налог
Range(«1:3»)строки с первой по третью
Range(«A1: C2 , B10:D24»)объединение двух несмежных интервалов A1: C2 и B10:D24
Range(«A1:C10 B10:D24»)пересечение двух интервалов A1:C10 и B10:D24, т.е. интервал B10:C10

Второе заявление

Аргументы задают координаты промежутка:

  • Cell1 — одиночная ячейка (строка или столбец), задающая левый верхний угол диапазона;
  • Cell2 — одиночная ячейка (строка или столбец), задающая правый нижний угол диапазона. Необязательный аргумент.

Аргументами могут быть переменные, выражения, свойства или методы, которые представляют объект Range — одну ячейку, одну строку или один столбец рабочего листа.

Примеры записи оператора Range (2 способ)

ЗаписьВозвращаемый объект
Range(«A5″,»D18»)интервал A5:D18
Range(Columns(1), Columns(5))интервал, содержащий первые пять столбцов рабочего листа
  • Если свойство Range применяется к объекту Range, ссылка на диапазон ячеек рассматривается как относительная, и возвращается смещенный объект Range.

Например, если выбран диапазон C1:D5, то запись Selection.Range(«B2») вернет ячейку D2.

Свойство Cells

Свойство Cells возвращает одну ячейку электронной таблицы, которая находится на пересечении строки и столбца, заданных целыми числами.

Синтаксис объект. Ячейки (индекс строки, индекс столбца)

  • Object — ссылка на объект. Ссылка необязательна. По умолчанию используется активный лист;
  • RowIndex — индекс строки;
  • ColumnIndex — индекс столбца.
  • В свойстве Cells индекс строки является первым аргументом, индекс столбца — вторым, а адрес ячейки A1 указывает сначала столбец, а затем строку.
  • Термин «индекс» ( Index, ColumnIndex, RowIndex ) всегда означает целое число, целочисленную переменную или выражение, результат которого является целым числом или может быть преобразован в целое число.
Примеры записи свойства Cells

ЗаписьКомментарийВозвращаемый объект
ActiveSheet. CellsСвойство Cells без аргументоввсе ячейки активного рабочего листа
Range(«C5:C10»). Cells (1,1)Свойство Cells применяется к объекту Range (относительная ссылка)ячейка C5
Range( Cells (7,3), Cells (10,4))Свойство Cells используется в качестве аргументов свойства Rangeинтервал ячеек C7:D10
Читайте так же:
Как округлить диапазон ячеек в Excel?
Свойство Offset

Вы можете указать количество строк и столбцов, отделяющих указанную ячейку от исходной, используя свойство Offset, то есть указывая смещение относительно выбранной ячейки. Например, Range(«A5»). Offset(-2,1) возвращает ячейку B3.

  • Объект — ссылка на объект Range. Ссылка является обязательной и определяет объект, относительно которого определяется смещение;
  • RowOffset — смещение строки искомой ячейки относительно исходной ячейки;
  • ColumnOffset — смещение столбца искомой ячейки относительно исходной ячейки.

Имеется необязательный аргумент RowOffset и аргумент смещения столбцов. Смещение равно нулю, если аргументы не указаны.

Например, если выбран диапазон C1:D5, запись Selection.Offset(2,1).Select будет выбирать диапазон D3:E7.

Метод Union и свойство Areas

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

Синтаксический объект. Союз (arg1,arg2. )

  • — всегда объект Application. Ссылка необязательна;
  • Arg1,arg2 — диапазоны ячеек. Количество аргументов произвольно. Обязательно наличие как минимум двух аргументов.

Например, оператор Union (Range(«A1:C5»),Range(«B10:D12»)).Select выделяет несмежные интервалы A1:C5 и B10:D12.

Объекты диапазона разделяются между объединенными интервалами свойством Areas.

  • Объект — ссылка на объект Range, состоящий из нескольких диапазонов;
  • Индекс — номер диапазона в объекте. Аргумент является необязательным.
Примеры

ОператорКомментарийРезультат
p= Union (Range(«A1:C5»), Range(«B10:D12»)).Areas(2).CountЕсли аргумент задан, то свойство Areas возвращает интервал — объект Range , определенный индексом интерваларавен девяти, так как во втором интервале ровно 9 ячеек
p= Union (Range(«A1:C5»), Range(«B10:D12»)).Areas.CountCвойство Areas без аргументов рассматривает каждый из несмежных интервалов как элемент коллекции объектов Rangeравен двум, так как объект, определенный методом Union , состоит из двух областей — коллекции из двух элементов
p=Range(«B10:D12»).Areas.Countравен единице, так как объект Range представляет один элемент коллекции
Свойства Column и Row (R/O Integer)

Properties возвращает целое число, указывающее индекс первого столбца или первой строки для данного объекта. Синтаксис свойств

  • Object — Обязательная ссылка на объект Range (Диапазон).

Например, запись Range(«C5»). Column возвращает число 3, а Range(«C5»). Строка возвращает число 5.

Свойства Columns и Rows

В отличие от свойства Column, свойство Columns используется для указания столбцов в таблице. возвращает объект Range, представляющий столбец или коллекцию столбцов в объекте, к которому было применено это свойство.

  • Object — ссылка на объект. Указание необязательно, по умолчанию используется активный рабочий лист;
  • Index — индекс колонки в объекте.
Читайте так же:
Как объединить несколько ячеек с разрывом строки в листе Google?

Например, запись Columns(1) возвращает столбец A активного рабочего листа и Range(«C1:D5»). В столбце C указанного интервала, а именно строк C1 — C5, запись Columns(1) возвращает набор ячеек.

  • Если индекс столбца не указан, все столбцы объекта будут возвращены как объект Range.
  • Индекс столбца может быть указан как число или буква, причем буква должна быть заключена в инвертированную запятую. Ссылки Columns(2) и Columns(«B») указывают на один и тот же столбец B.

Свойство Rows (не путать со свойством Row!) возвращает объект Range, представляющий строку или набор строк в объекте, к которому применяется это свойство.

  • Object — ссылка на объект. Спецификация необязательна, по умолчанию используется активный лист ;
  • Index — индекс строки в объекте.
  • Если номер строки не указан, все строки объекта будут возвращены в виде объекта Range.

Например, оператор nr=Selection. Rows(Selection. Rows. Count). Эта функция возвращает номер последней строки в выбранном диапазоне ячеек.

Свойство CurrentRegion

Свойство CurrentRegion определяет объект Range, который соответствует диапазону ячеек, включая указанную ячейку.

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

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

Пример работы со свойством CurrentRegion

Range(«A1»).ColumnWidth=15 устанавливает ширину колонки A в 15 символов

Range(«A1»).Width возвращает значение 93.75, если ширина колонки составляет 15 символов, шрифт Times New Roman, размер шрифта 12 пунктов (72 пункта соответствует 1 дюйму или примерно 2,54 см).

Отладка. Print Range(«A1:C3»). 8.43 и Debug печатается для ColumnWidth. Print Range(«A1:C3»). 144 будет напечатано, если для столбцов установлена стандартная ширина, шрифт Arial Cyr и размер 10

ActiveCell. RowHeight = 14 устанавливает высоту ряда, в котором находится активная ячейка, равной 14 точкам

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

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector