Evolcom.ru

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

Как подсчитать ячейки с определенным текстом и цветом заливки / шрифта в Excel?

СчетЯчеек_Шрифт

Функция подсчитывает количество ячеек, шрифт в которых имеет определенный цвет. Так же есть возможность указать отдельный критерий(например, подсчитывать только ячейки с красным цветом шрифта и напротив которых содержится слово «расход»).
Для чего это нужно? Скорее всего Вы в работе с Excel уже сталкивались с таблицами, ячейки которых окрашены в тот или иной цвет заливки либо шрифта. Например, Желтый — расходы Транспортного отдела, Красный — Экономического, Зеленый — Администрация и т.п. И необходимо все эти расходы просуммировать/подсчитать, но опираясь на ячейки с определенным цветом заливки/шрифта. В Excel до сих пор нет ни одной функции для суммирования/подсчета данных в ячейках с определенным цветом заливки или шрифта.

Вызовите команду через стандартное диалоговое окно:.

Функция Мастер-Категория «MulTEx»-Счет_Шрифт

Вызов с панели MulTEx:

Сумма/Поиск/Функции — Математические — Подсчет клеток_Фонт

Синтаксис:
=СчетЯчеек_Шрифт( $E$2:$E$20 ; $E$7 ; I13 ; $A$2:$A$20 ; $B$2:$B$20 )
=СчетЯчеек_Шрифт( $E$2:$E$20 ; $E$7 )
=СчетЯчеек_Шрифт( $E$2:$E$20 ; $E$7 ; I13 )
=СчетЯчеек_Шрифт( $E$2:$E$20 ; $E$7 ; I13 ; $A$2:$A$20 )

Диапазон значений от $E$2 до $E$20 может быть подсчитан через RangeAccount( $E$2:$E$20 ). Вы можете указать несколько столбцов. Столбец с критерием (если вы планируете считать и по критерию) не обязательно должен находиться в диапазоне. В этом столбце по умолчанию проверяется цвет шрифта.

CellSample( $E$7 ) — Образец ячейки с цветом шрифта, который нужно подсчитать.

Критерий( I13 ) — необязательный аргумент. Если указан, то подсчитываются ячейки с указанным критерием и цветом заливки. По умолчанию Критерий просматривается в ДиапазонеСчета, но если указан ДиапазонКритерия, то Критерий просматривается в ДиапазонеКритерия. Допускается применение в критерии символов подстановки — «*» и «?» . Например, для подсчета только ячеек, в которых содержится слово «отчет» необходимо указать в качестве критерия — «*отчет*» . Если необходимо посчитать количество непустых ячеек с указанным цветом шрифта, то можно указать критерий: «*?*» . Если не указан, то подсчитываются все ячейки с указанным цветом шрифта.
Так же данный аргумент может принимать в качестве критерия символы сравнения ( , =, <>, ):

  • «>0» — будут подсчитаны все ячейки в ДиапазонеСчета, значения ячеек критериев для которых больше нуля;
  • «>=2» — будут подсчитаны все ячейки в ДиапазонеСчета, значения ячеек критериев для которых больше или равно двум;
  • » — будут подсчитаны все ячейки в ДиапазонеСчета, значения ячеек критериев для которых меньше нуля;
  • » — будут подсчитаны все ячейки в ДиапазонеСчета, значения ячеек критериев для которых меньше или равно 60;
  • «<>0″ — будут подсчитаны все ячейки в ДиапазонеСчета, значения ячеек критериев для которых не равно нулю;
  • «<>» — будут подсчитаны все ячейки в ДиапазонеСчета, значения ячеек критериев для которых не пустые;
  • «*отчет*» — будут подсчитаны все ячейки в ДиапазонеСчета, значения ячеек критериев для которых содержит слово «отчет»;
Читайте так же:
Как показать стрелку выпадающего списка в Excel?

Вместо нуля может быть любое число или текст. Так же можно добавить ссылку на ячейку со значением: «<>«&D$1

RangeCriteria( $A$2:$A$20 ) — Необязательный аргумент. Указывает диапазон, в котором следует искать критерии (если критерии указаны). RangeCriterion должен быть равен по количеству ячеек Account Range. Если указан RangeCriterion, то он идентичен цвету заливки (если не указан RangeColor). Если CriterionRange не указан, критерий отображается в Billing Range.

ExUF() — необязательный аргумент. Он может быть установлен в TRUE или FALSE. По умолчанию установлено значение TRUE. Функция будет подсчитывать ячейки, к которым применено условное форматирование, если установлено значение TRUE. Если установлено значение FALSE, функция будет подсчитывать ячейки без учета примененного условного форматирования. Даже если применено условное форматирование и ячейка закрашена им, а фактический цвет заливки не совпадает с цветом ячейки-образца — она не будет подсчитана.

RangeColor( $B$2:$B$20 ) — Необязательный аргумент. Указывает, должен ли проверяемый цвет шрифта быть виден в диапазоне, отличном от CriteriaRange или BillingRange. По умолчанию цвет шрифта проверяется в BillingRange, если не указан CriteriaRange или ColourRange. Однако, если указан параметрColourRange, цвет заливки проверяется в этом диапазоне. Если не указан диапазон цветов, но указан диапазон цветов, цвет шрифта проверяется в диапазоне цветов.

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

Вы не можете изменить цвет шрифта, не повлияв на функцию. После изменения параметров выделите ячейку и нажмите F2 — Enter для повторного вычисления функции. Или нажмите Shift + F9 (пересчет функций активного листа) или нажмите F9 (пересчет функций всей книги).

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

Анализ документов Word с использованием Python

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

Читайте так же:
Как подсчитать количество вхождений в диапазон дат и времени в Excel?

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

Чтобы установить библиотеку из командной строки, введите:

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

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

Мы просмотрели все папки и вывели список всех файлов с расширением docx. Файлы, начинающиеся с тильды, игнорировались (эти временные файлы появляются только при открытии документа в Windows). Теперь, когда у нас есть список всех документов, мы можем начать работать с ними:

На каждом шаге цикла в переменную doc записывается экземпляр, представляющий весь документ. Мы можем увидеть основные свойства такого документа:

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

Теперь рассмотрим, как можно изучить содержимое документа. Документы с расширением docx имеют развитую внутреннюю структуру, которая характеризуется следующими объектами библиотеки docx:

Объект «Документ», представляющий весь документ

  • Список объектов Paragraph – абзацы документа
    * Список объектов Run – фрагменты текста с различными стилями форматирования (курсив, цвет шрифта и т.п.)
  • Список объектов Table – таблицы документа
    * Список объектов Row – строки таблицы
    * Список объектов Cell – ячейки в строке
    * Список объектов Column – столбцы таблицы
    * Список объектов Cell – ячейки в столбце
  • Список объектов InlineShape — Иллюстрации к документам
Читайте так же:
Как предотвратить ввод строчных букв в выделении Excel?

Работа с документальными текстами

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

Частая задача — извлечь весь текст из документа для дальнейшей обработки. Для этого достаточно прочитать все абзацы документа:

Как мы видим, чтобы получить текст абзаца, нам достаточно обратиться к объекту paragraph.text. Но что, если мы хотим извлекать только параграфы с определенными характеристиками и работать только с ними? Давайте рассмотрим основные характеристики абзацев, которые можно разобрать.

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

Значения выравнивания соответствуют одному из основных стилей выравнивания: LEFT (0), center (1), RIGHT (2) или justification (3). Однако если пользователь не определил стиль выравнивания, значение выравнивания будет равно None.

Также можно получить значения отступов для абзацев документа:

Как и в предыдущем примере, если отступы не были заданы, значения параметров будут None. Иногда они будут представлены как целое число в английских метрических единицах (EMU). С помощью этого формата можно преобразовывать числа как в метрические, так и в английские метрические единицы. Преобразовать эти числа в привычные форматы довольно просто, достаточно добавить нужные единицы после параметра (например, formatting.space_before.cm или formatting.space_before.pt). Это преобразование не может быть применено к значениям None.

Наконец, вы можете посмотреть на положение абзаца на странице. В меню Абзац… вкладка Положение страницы содержит четыре параметра, значения которых также можно просмотреть с помощью библиотеки docx:

Параметры устанавливаются в None, если пользователь не проверил их, и в True, если пользователь проверил их.

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

Читайте так же:
Как перевернуть / перевернуть столбец порядка данных по вертикали в Excel?

Вы можете получить список отрывков с различными стилями форматирования (Run list). Например, постарайтесь уловить все отрывки, выделенные курсивом:

Очень просто, не правда ли? Вот еще несколько стилей форматирования, которые мы можем извлечь:

Если пользователь не изменил стиль форматирования (без подчеркивания, стандартным шрифтом и т.д.), параметры будут иметь значение None. Но если стиль определенного параметра был изменен, то. :

  • параметры italic, bold, underline, strike будут иметь значение True;
  • параметр font.name – наименование шрифта;
  • параметр font.color.rgb – код цвета текста в RGB;
  • параметр font.highlight_color – наименование цвета заливки текста.

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

Абзацы и фрагменты абзацев могут быть оформлены в соответствии со стилями Word (например, Normal, Heading 1, Intense Quote). Чем это может быть полезно? Использование стилей абзацев, например, может помочь выделить маркированные или нумерованные списки. Каждый элемент списка рассматривается как отдельный абзац, но каждый элемент имеет свой формат: абзац списка. Если вы хотите извлечь только элементы из списка, вы можете использовать следующий код:

Давайте рассмотрим еще менее тривиальный случай, чтобы закрепить полученные знания. Рассмотрим набор документов с похожей структурой, из которых необходимо извлечь названия продуктов. На основе анализа документов мы обнаружили, что продукты вставляются только в абзацы, которые начинаются с новой страницы и выравниваются по ширине. Более того, сами названия написаны жирным шрифтом Arial Narrow. Давайте посмотрим, как мы можем проанализировать документы:

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

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

Читайте так же:
Как переместить определенную строку на новый лист, если в Excel установлен флажок?

Работаем с таблицами

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

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

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

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

Для каждой из таблиц документов с помощью этого кода можно получить координаты всех объединенных ячеек. Разница в координатах tc.top и tc.bottom показывает, сколько строк находится в объединенной ячейке, а разница в tc.left и tc.right — сколько столбцов.

Наконец, вы можете выбрать ячейки, фон которых имеет определенный цвет. Это можно сделать, просмотрев xml-код ячейки с помощью регулярных выражений:

В этом блоке кода выделены только те ячейки, фон которых желтый (#FFFFFF00 в RGB-формате).

Работайте с иллюстрациями

Документы D OCX могут также включать иллюстрации. Используя стандартные методы, можно проверить только размер изображения:

Однако, используя стороннюю библиотеку docx2txt и разбирая xml-код абзацев, можно не только загрузить все иллюстрации из документов, но и определить, в каком абзаце они появились:

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

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