677

Сумма ячеек по цвету, шрифту, формату и т.д.

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

1. Сохраняем документ с поддержкой макросов, хотя его там и не будет, но в данном случае так надо.

2. Нажатием Ctrl+F3 или на вкладке «Формулы» выбираем «Диспетчер имён», где в строке «Имя» вводим название формулы ЦветЯчейки, а в поле «Диапазон» саму формулу: =ПОЛУЧИТЬ.ЯЧЕЙКУ(63;ДВССЫЛ("RC[-1]";0))

=GET.CELL(63;INDIRECT("RC[-1]";0))

Сумма ячеек по цвету, шрифту, формату и т.д. Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

Я не буду грузить читателя разъяснением значений этой формулы, главное, чтобы всё работало, не так ли?)) Отмечу лишь, что 63 это код цвета заливки, заменяя который, можно подсчитывать ячейки по формату, шрифту, выделению курсивом и т.п.

Вот таблица с кодами и их значениями:

Сумма ячеек по цвету, шрифту, формату и т.д. Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

3. В соседнюю от цветной ячейки вводим «равно» и имя созданной формулы ЦветЯчейки, затем протягиваем её вниз. В результате отображаются коды цветов, которые будут необходимы нам для подсчёта.

Сумма ячеек по цвету, шрифту, формату и т.д. Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

4. Теперь с помощью формулы =СУММЕСЛИ(B2:B12;10;A2:A12) можно посчитать сумму цветных ячеек, где 10 это код цвета, для жёлтых - 6:

=SUMIF(B2:B12;10;A2:A12)

Сумма ячеек по цвету, шрифту, формату и т.д. Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

Созданная формула =ЦветЯчейки определяет 56 цветов. На некоторые цветовые гаммы он реагирует некорректно, так что любителям 50 оттенков серого придётся воспользоваться хардкорным чёрным или серым))

Вот пример палитры «твёрдых» цветов:

Сумма ячеек по цвету, шрифту, формату и т.д. Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

Следует учесть, что Excel при изменении цвета ячейки автоматически не совершит пересчёт данных, для этого необходимо заново ввести формулу, либо нажать Ctlr+Alt+F9, что гораздо проще и быстрее.


Также на этот случай есть макрос,


1. Нажимаем Alt+F11 и в открывшемся окне вводим следующий текст


Public Function SumByColor(DataRange As Range, ColorSample As Range) As Double

Dim Sum As Double

Application.Volatile True


For Each cell In DataRange

If cell.Interior.Color = ColorSample.Interior.Color Then

Sum = Sum + cell.Value

End If

Next cell

SumByColor = Sum

End Function


2. В разделе «Формулы» - «Вставить функцию» выбираем категорию «Определенные пользователем», где указываем нашу функцию, которой задаём диапазон подсчёта и образец цвета:

Сумма ячеек по цвету, шрифту, формату и т.д. Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

И получаем результат:

Сумма ячеек по цвету, шрифту, формату и т.д. Excel, Таблица, Лайфхак, Сумма, Цвет, Полезное, Длиннопост

Чтобы макрос учитывал не цвет заливки фона, а цвет шрифта ячейки, в шестой строке заменяем свойство Interior на Font в обеих частях выражения.


Для подсчёта количества цветных ячеек, а не суммы, заменяем в седьмой строке Sum = Sum + cell.Value на Sum = Sum + 1


Как и в первом варианте, макрос также не может подсчитать сумму ячеек автоматически, поэтому после произведённых изменений нажимаем клавишу F9.


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

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


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


P.S. Уважаемые подписчики, посты про Excel будут выходить по понедельникам

Найдены дубликаты

+9

Дяденька, а можно на Облако выкладывать файл-образец с формулами из поста? Чтобы потыкаться и скопировать себе нужное. Чтобы понять быстрее, как работает эта функция. А то получайникам сложно, а ужас как нужно это дело!! ))

раскрыть ветку 15
+3

Ссылка на файл в облаке

https://cloud.mail.ru/public/2JjJ/21PPk2kzU

+1

На каком сайте лучше в облаке выкладывать? Некоторые боятся скачивать файлы с неизвестных источников.

раскрыть ветку 8
+5

Можно создать папочку, расшарить ее и туда добавлять файлы согласно выходу постов. Вышел 12 ноября - «2019, ноябрь 12 - Сумма ячеек по цвету, шрифту, формату и т.д.»


Начало продумать - чтобы сортировка была красивая.


И всегда ссылку в постах одну - на эту папочку.


И места мало будет занимать и людям удобно.

И в файле можно ссылку на пост делать - чтобы нашёл файл, и оттуда на пост пошёл )


Спасибо! Очень жду по цвету!!!

раскрыть ветку 4
+1
google docs? и ничего скачивать не надо, и функционал, как я понимаю полный.
+1

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

0

GitHub

+1

Поддерживаю!

-1

Качать файл с поддержкой макросов от анонимуса из интернета? Ну так себе идейка. Тем более совсем получайник не найдет как эти самые макросы разрешить.

Попробуйте пошагово сделать ровно то, что в посте.

раскрыть ветку 3
0

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

0

Гугл «как включить макросы», первый же запрос на сайт Майкрософта ведёт. Там два клика...

раскрыть ветку 1
+4
Я не буду грузить читателя разъяснением значений этой формулы, главное, чтобы всё работало, не так ли?

Нет, не так, грузи давай)))

+2
Только в пятницу грузился, как такое сделать, интернет молчал. Спаситель😀
раскрыть ветку 1
0

Иногда помощь приходит оттуда, откуда её не ждёшь)

+2

Спасибушки, полезненько!

+1

Спасибо, в закладки

+1
Вместо макроса для суммы и количества достаточно использовать суммесли и счетесли
+1

Как же это вовремя! Как раз надо было! Спасибо!

0

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

раскрыть ветку 3
0

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

раскрыть ветку 2
0

Да это понятно. Хотелось бы автоматический способ.

раскрыть ветку 1
0

Я прошу прощения, но вы не могли бы писать (допустим в скобках) и английские клманды тоже? Пожалуйста. :)

раскрыть ветку 3
0

Я же указал

раскрыть ветку 2
0

Чет у меня не пашет :(

раскрыть ветку 1
0

В word есть такая штука, как рассылка.

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


Есть ли в excel что-то подобное?

Например есть список гостей и пригласительный сверстаный в excel. И нужно для каждого гостя распечатать пригласительный.

раскрыть ветку 9
0

Попробуйте просто запись макроса. Если разовая задача- то это будет проще, чем разбираться с VBA.

раскрыть ветку 8
0

Я через Apache POI делаю сейчас. Получается книга из 1600 листов, приходится бить на несколько файлов.

раскрыть ветку 7
0

значимое форматирование - от лукавого

начнутся потом формулы, которые меняют ячейки в зависимости от цвета..

оформление должно оставаться оформлением


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


тогда информация будет полной и экспортируемой вне зависимости от используемого инструмента

0

А впр по цвету ячейки есть?

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

Первый раз использую пикабу, как "ответы майл", но может кто знает, как подсчитать сумму чисел, написанных в одной ячейке через запятую?=)))

раскрыть ветку 3
0

1. Самый быстрый и простой способ. Выделить ячейку, нажать Ctrl+H, "Найти" - запятую, "Заменить на" - знак плюс. Потом просто в начале формулы вставить "=" и нажать Enter.

2. Чуть менее быстрый способ. Скопировать ячейку/всю колонку с такими ячейками в Блокнот, сохранить в нем файл с расширением CSV (для этого выбрать формат "Все файлы" при сохранении), затем в Excel выбрать Данные - Из текста/CSV, указать сохраненный файл, при необходимости указать, каким символом разделены колонки (по умолчанию - запятой), получится таблица, где все числа встанут в отдельных колонках. Просуммировать все колонки. Этот способ имеет смысл, когда надо еще что-нибудь делать с цифрами.


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

раскрыть ветку 2
0

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

0

второй пункт (для Exсel 2007 и старше) уже реализован в стандартной функции Данные/Текст по столбцам

0
По поводу применения макроса написано очень кратко и не соответствует тому, что нужно делать на самом деле. Хотя бы от альт и эф11 не открывается никакого поля ввода для текста. Нужно создавать модуль. Если просто вставить этот код в модуль, работать тоже не будет, нужно ещё прикрутить вызов функции, наверное и само тело Sub-a.
раскрыть ветку 2
0

У вас документ сохранен в формате с поддержкой макросов?

раскрыть ветку 1
0
Создаю документ, созраняю с поддержкой. При нажатии комбинации открывается, окно с названиями страниц, книги, и можно нажать ран или дебаг вверху на панельке. При переключении на ввод кода открывается поле для кода. Вставляем скопированный текст, нажимаем ран. Предлагает ввести имя макроса. Вводим например макрос1, открывается другое окошко для ввода кода, где
макрос1()
end function
Уже введено. При вставки текста между началом и концом, компилятор выдает ошибку, при полной замене тоже не получилось.
Посмотрел оформление других макросов, нужно же через sub делать и вызывать функции внутри suba?
просто до этого я ни разу не пробовал макросы.
0

@Veseliy.4el Я часто  использую автоформатирование ячеек по цвету ("Условное форматирование" - "Гистограмма" или аналог. ) Вы не проверяли - может ли описанный вами макрос "переварить" такие автопокрашенные столбцы?

раскрыть ветку 1
+2

Если гистограмма сплошного окрашивания и она установленного цвета, по идее должна сработать, но я не проверял. Эта формула схожие оттенки выдает одинаковым кодом

0

А можно от обратного сделать? Т.е. в зависимости от значения в ячейке строка закрашивалась в определенный цвет? Например: в строке Исполнитель ячейка "Иван" и вся строка становится зеленой.

раскрыть ветку 3
0

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

раскрыть ветку 2
0

Но там же не строка, а конкретная ячейка будет закрашиваться?

раскрыть ветку 1
0

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

раскрыть ветку 1
0

Она настолько стара, что любое упоминание о ней выпилено с сайта MS

0

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

0

А можно сделать сумму ячеек по ключевому слову в ячейке слева от нее?

раскрыть ветку 8
+1
раскрыть ветку 7
0

А если нужно несколько диапозонов собрать?

0

СУММЕСЛИ(B2:B15;"Доход";C2:C15)

эта формула отбирает ячейки только со значением "Доход"

Как сделать так чтобы в ход шли ячейки с другими значениями но содержащие ключевое слово "доход"? Вышеуказанная формула будет пропускать ячейки со значением "Доход 1", Доход от продажи ручки", "Вася какой то доход яга"

раскрыть ветку 5
0

Я обычно просто фильтрую по цвету и снижу ставлю сумму.

раскрыть ветку 1
+2

Посмотрите мой предыдущий пост формула =СУММЕСЛИ(B2:B15;"Доход";C2:C15) слово доход замените на своё

-1

Type_num Returns


1 Absolute reference of the upper-left cell in reference, as text in the current workspace reference style.

2 Row number of the top cell in reference.

3 Column number of the leftmost cell in reference.

4 Same as TYPE(reference).

5 Contents of reference.

6 Formula in reference, as text, in either A1 or R1C1 style depending on the workspace setting.

7 Number format of the cell, as text (for example, "m/d/yy" or "General").

8 Number indicating the cell's horizontal alignment:

1 = General


2 = Left


3 = Center


4 = Right


5 = Fill


6 = Justify


7 = Center across cells


9 Number indicating the left-border style assigned to the cell:



0 = No border


1 = Thin line


2 = Medium line


3 = Dashed line


4 = Dotted line


5 = Thick line


6 = Double line


7 = Hairline


10 Number indicating the right-border style assigned to the cell.


See type_num 9 for descriptions of the numbers returned.


11 Number indicating the top-border style assigned to the cell.


See type_num 9 for descriptions of the numbers returned.


12 Number indicating the bottom-border style assigned to the cell.


See type_num 9 for descriptions of the numbers returned.


13 Number from 0 to 18, indicating the pattern of the selected cell


as displayed in the Patterns tab of the Format Cells dialog box,


which appears when you choose the Cells command from the Format menu.


If no pattern is selected, returns 0.


14 If the cell is locked, returns TRUE; otherwise, returns FALSE.


15 If the cell's formula is hidden, returns TRUE; otherwise, returns FALSE.


16 A two-item horizontal array containing the width of the active cell and a logical value


indicating whether the cell's width is set to change as the standard width changes (TRUE)


or is a custom width (FALSE).


17 Row height of cell, in points.


18 Name of font, as text.


19 Size of font, in points.


20 If all the characters in the cell, or only the first character, are bold, returns TRUE; otherwise, returns FALSE.


21 If all the characters in the cell, or only the first character, are italic, returns TRUE; otherwise, returns FALSE.


22 If all the characters in the cell, or only the first character, are underlined, returns TRUE; otherwise, returns FALSE.


23 If all the characters in the cell, or only the first character, are struck through, returns TRUE; otherwise, returns FALSE.


24 Font color of the first character in the cell, as a number in the range 1 to 56. If font color is automatic, returns 0.


25 If all the characters in the cell, or only the first character, are outlined, returns TRUE; otherwise, returns FALSE.


Outline font format is not supported by Microsoft Excel for Windows.


26 If all the characters in the cell, or only the first character, are shadowed, returns TRUE; otherwise, returns FALSE.


Shadow font format is not supported by Microsoft Excel for Windows.


27 Number indicating whether a manual page break occurs at the cell:


0 = No break


1 = Row


2 = Column


3 = Both row and column


28 Row level (outline).


29 Column level (outline).


30 If the row containing the active cell is a summary row, returns TRUE; otherwise, returns FALSE.


31 If the column containing the active cell is a summary column, returns TRUE; otherwise, returns FALSE.


32 Name of the workbook and sheet containing the cell If the window contains only a single sheet that has the same


name as the workbook without its extension, returns only the name of the book, in the form BOOK1.XLS.


Otherwise, returns the name of the sheet in the form "[Book1]Sheet1".


33 If the cell is formatted to wrap, returns TRUE; otherwise, returns FALSE.


34 Left-border color as a number in the range 1 to 56. If color is automatic, returns 0.


35 Right-border color as a number in the range 1 to 56. If color is automatic, returns 0.


36 Top-border color as a number in the range 1 to 56. If color is automatic, returns 0.


37 Bottom-border color as a number in the range 1 to 56. If color is automatic, returns 0.


38 Shade foreground color as a number in the range 1 to 56. If color is automatic, returns 0.


39 Shade background color as a number in the range 1 to 56. If color is automatic, returns 0.


40 Style of the cell, as text.


41 Returns the formula in the active cell without translating it (useful for international macro sheets).


42 The horizontal distance, measured in points, from the left edge of the active window to the left edge of the cell.


May be a negative number if the window is scrolled beyond the cell.


43 The vertical distance, measured in points, from the top edge of the active window to the top edge of the cell.


May be a negative number if the window is scrolled beyond the cell.


44 The horizontal distance, measured in points, from the left edge of the active window to the right edge of the cell.


May be a negative number if the window is scrolled beyond the cell.


45 The vertical distance, measured in points, from the top edge of the active window to the bottom edge of the cell.


May be a negative number if the window is scrolled beyond the cell.


46 If the cell contains a text note, returns TRUE; otherwise, returns FALSE.


47 If the cell contains a sound note, returns TRUE; otherwise, returns FALSE.


48 If the cells contains a formula, returns TRUE; if a constant, returns FALSE.


49 If the cell is part of an array, returns TRUE; otherwise, returns FALSE.


50 Number indicating the cell's vertical alignment:


1 = Top


2 = Center


3 = Bottom


4 = Justified


51 Number indicating the cell's vertical orientation:


0 = Horizontal


1 = Vertical


2 = Upward


3 = Downward


52 The cell prefix (or text alignment) character, or empty text ("") if the cell does not contain one.


53 Contents of the cell as it is currently displayed, as text, including any additional numbers or symbols


resulting from the cell's formatting.


54 Returns the name of the PivotTable view containing the active cell.


55 Returns the position of a cell within the PivotTableView.


56 Returns the name of the field containing the active cell reference if inside a PivotTable view.


57 Returns TRUE if all the characters in the cell, or only the first character, are formatted with a superscript font;


otherwise, returns FALSE.


58 Returns the font style as text of all the characters in the cell, or only the first character as displayed in the


Font tab of the Format Cells dialog box: for example, "Bold Italic".


59 Returns the number for the underline style:


1 = none


2 = single


3 = double


4 = single accounting


5 = double accounting


60 Returns TRUE if all the characters in the cell, or only the first characrter, are formatted with a subscript font;


otherwise, it returns FALSE.


61 Returns the name of the PivotTable item for the active cell, as text.

62 Returns the name of the workbook and the current sheet in the form "[book1]sheet1".

63 Returns the fill (background) color of the cell.

64 Returns the pattern (foreground) color of the cell.

65 Returns TRUE if the Add Indent alignment option is on (Far East versions of Microsoft Excel only);

otherwise, it returns FALSE.

66 Returns the book name of the workbook containing the cell in the form BOOK1.XLS.



стырено вот отсюда

https://www.mrexcel.com/forum/excel-questions/20611-info-onl...
Похожие посты
Возможно, вас заинтересуют другие посты по тегам: