Сумма ячеек по цвету, шрифту, формату и т.д.
Допустим вы используете таблицу с числовыми данными, которые для удобства помечаете цветом для последующего их подсчёта. Исходя из названия заголовка рассмотрим, как такое можно реализовать в Excel.
1. Сохраняем документ с поддержкой макросов, хотя его там и не будет, но в данном случае так надо.
2. Нажатием Ctrl+F3 или на вкладке «Формулы» выбираем «Диспетчер имён», где в строке «Имя» вводим название формулы ЦветЯчейки, а в поле «Диапазон» саму формулу: =ПОЛУЧИТЬ.ЯЧЕЙКУ(63;ДВССЫЛ("RC[-1]";0))
=GET.CELL(63;INDIRECT("RC[-1]";0))
Я не буду грузить читателя разъяснением значений этой формулы, главное, чтобы всё работало, не так ли?)) Отмечу лишь, что 63 это код цвета заливки, заменяя который, можно подсчитывать ячейки по формату, шрифту, выделению курсивом и т.п.
Вот таблица с кодами и их значениями:
3. В соседнюю от цветной ячейки вводим «равно» и имя созданной формулы ЦветЯчейки, затем протягиваем её вниз. В результате отображаются коды цветов, которые будут необходимы нам для подсчёта.
4. Теперь с помощью формулы =СУММЕСЛИ(B2:B12;10;A2:A12) можно посчитать сумму цветных ячеек, где 10 это код цвета, для жёлтых - 6:
=SUMIF(B2:B12;10;A2:A12)
Созданная формула =ЦветЯчейки определяет 56 цветов. На некоторые цветовые гаммы он реагирует некорректно, так что любителям 50 оттенков серого придётся воспользоваться хардкорным чёрным или серым))
Вот пример палитры «твёрдых» цветов:
Следует учесть, что 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. В разделе «Формулы» - «Вставить функцию» выбираем категорию «Определенные пользователем», где указываем нашу функцию, которой задаём диапазон подсчёта и образец цвета:
И получаем результат:
Чтобы макрос учитывал не цвет заливки фона, а цвет шрифта ячейки, в шестой строке заменяем свойство Interior на Font в обеих частях выражения.
Для подсчёта количества цветных ячеек, а не суммы, заменяем в седьмой строке Sum = Sum + cell.Value на Sum = Sum + 1
Как и в первом варианте, макрос также не может подсчитать сумму ячеек автоматически, поэтому после произведённых изменений нажимаем клавишу F9.
Если значения в Вашей таблице окрашены в различные цвета и нужно их пересчитать по отдельности, то просто замените ячейку с образцом на цвет из таблицы и нажав F9 получите мгновенный результат.
Также используя ту же формулу, заменив лишь ячейку с образцом, можно вывести сумму и в другой ячейке.
Следует учесть, что функция перебирает все, в том числе и пустые ячейки, в DataRange, поэтому задавайте в качестве первого аргумента только диапазон со значениями, а не весь столбец, иначе Excel «зависнет» надолго.
Дяденька, а можно на Облако выкладывать файл-образец с формулами из поста? Чтобы потыкаться и скопировать себе нужное. Чтобы понять быстрее, как работает эта функция. А то получайникам сложно, а ужас как нужно это дело!! ))
Ссылка на файл в облаке
https://cloud.mail.ru/public/2JjJ/21PPk2kzU
На каком сайте лучше в облаке выкладывать? Некоторые боятся скачивать файлы с неизвестных источников.
Можно создать папочку, расшарить ее и туда добавлять файлы согласно выходу постов. Вышел 12 ноября - «2019, ноябрь 12 - Сумма ячеек по цвету, шрифту, формату и т.д.»
Начало продумать - чтобы сортировка была красивая.
И всегда ссылку в постах одну - на эту папочку.
И места мало будет занимать и людям удобно.
И в файле можно ссылку на пост делать - чтобы нашёл файл, и оттуда на пост пошёл )
Спасибо! Очень жду по цвету!!!
Сегодня сделаю в облаке и выложу электронный вариант
Выпадающий список? В нём будут определенные слова, при выборе которых они будут отображаться в ячейке, так?
Например Облако мейл ру, главное не архив, а сам файл екселя. И там идёт открытие сначала в браузере же, и проверка на вирусы тоже есть
GitHub
Поддерживаю!
Качать файл с поддержкой макросов от анонимуса из интернета? Ну так себе идейка. Тем более совсем получайник не найдет как эти самые макросы разрешить.
Попробуйте пошагово сделать ровно то, что в посте.
Я стараюсь подробно описать порядок действий, чтобы даже самый неопытный пользователь смог воспользоваться этими формулами
Гугл «как включить макросы», первый же запрос на сайт Майкрософта ведёт. Там два клика...
И остается получайник с разрешенными макросами.
Нет, не так, грузи давай)))
Иногда помощь приходит оттуда, откуда её не ждёшь)
Спасибушки, полезненько!
Спасибо, в закладки
Как же это вовремя! Как раз надо было! Спасибо!
Дружище подскажи есть ли возможность заполнять таблицы вниз? Например первая строка пустая во второй последнее введённое значение. В первую строку вводим данные, она опускается вниз и становится второй, вторая третьей и так далее?
Просто сделайте добавить строку перед ячейкой со значением, всё вниз и опустится
Да это понятно. Хотелось бы автоматический способ.
Увы мне такой способ не знаком
Я прошу прощения, но вы не могли бы писать (допустим в скобках) и английские клманды тоже? Пожалуйста. :)
Я же указал
Чет у меня не пашет :(
Документ сохранен с поддержкой макросов?
В word есть такая штука, как рассылка.
В документ можно из таблицы подставить колонки и потом распечатать для каждой строки из таблицы свой документ.
Есть ли в excel что-то подобное?
Например есть список гостей и пригласительный сверстаный в excel. И нужно для каждого гостя распечатать пригласительный.
Попробуйте просто запись макроса. Если разовая задача- то это будет проще, чем разбираться с VBA.
Я через Apache POI делаю сейчас. Получается книга из 1600 листов, приходится бить на несколько файлов.
А зачем эта книга вообще? Задача ведь: изменить ФИО-отправить на печать- выбрать следующего адресата-изменить ФИО и т.д.
Данные у вас и так имеются в отдельной таблице.
Ну или печатайте сначала в ПДФ с названием файла в виде ФИО, если перед печатью надо все проверить. А потом просто скопом отправляйте это барахло на принтер.
По итогу или 10 минут в записи и опробовании макроса и 1600 нажатий клавиш, или еще 20-40 минут на изучение как работают переменные в VBA и как найти кол-во строк - и у вас все генерится по одной кнопке. Это если с VBA вообще не сталкивался.
Документ не для меня, я лишь исполнитель.
Ну если сейчас все работает и всех устраивает -менять только проблем наживёте ))
Но попробуйте просто для себя заколбасить это в екселе без апача. Ессно, поинтересовавшись, а чего с этими 160 страничными доками потом делают.
А то окажется, что печатают этикетки. Где в программах для принтера уже имеются вот эти все функции вытаскивания данных из таблицы.
Вы близки к истине.
Этикетки это word документы. Из них нужно протоколы ОТК сделать по шаблону. Из них и из таблички.
Все это должно было быть автоматизированно, но как обычно на предприятиях бываете, проверка нагрянула а ничего не настроено.
BarTender из ексель- таблицы данные вытягивает. ZebraDesigner тоже.
Или у вас там хитрый принтер этикеток какой?
Но по скудным от вас сообщениям как и чего, даже догадки сложно строить )) Может быть и помог бы чем.
Этикетки это обычный лист А4 в ворд документе. Не пользуются они специализированным софтом.
Каждая этикетка в течении года руками составлялась. И фактически только там ТТХ изделия есть.
Теперь же нужно из таблицы эксель с перечнем изделий и некоторыми параметрами для каждой строки распарить этикетку. И потом для каждой строки сгенерировать заполненный документ на основе данных из таблицы и этикетки.
И я это сделал, но через POI, т.к. мне проще было на scala написать, чем с VBA разбираться.
Реализуйте все в екселе. Из ворда довольно легко все перекинуть.
Заодно многоязычность можно прикрутить и всякие проверки заполнения.
http://www.responsiblemineralsinitiative.org/media/docs/RMI_...
Вот тут вариант реализации. Покопайтесь, очень интересный опросник. @Veseliy.4el может и на его основе пару постов забабахает. И на все ТТХ сделать один документ с выбором из списка. Однокнопочные сотрудники будут рады.
А уж сгенерировать нечто (в ПДФ-е для распечатки или новый документ) можно или макросом или через VBA. Главное иметь данные в табличном виде, а не разбросанные по вордовским документам. Особенно если там разная верстка и делали это разные люди.
значимое форматирование - от лукавого
начнутся потом формулы, которые меняют ячейки в зависимости от цвета..
оформление должно оставаться оформлением
сортировку, фильтры и т.д. нужно основывать на информации, хранимой в самой таблице где-нибудь во вспомогательных ячейках - по ним же делать и условное форматирование
тогда информация будет полной и экспортируемой вне зависимости от используемого инструмента
А впр по цвету ячейки есть?
Первый раз использую пикабу, как "ответы майл", но может кто знает, как подсчитать сумму чисел, написанных в одной ячейке через запятую?=)))
1. Самый быстрый и простой способ. Выделить ячейку, нажать Ctrl+H, "Найти" - запятую, "Заменить на" - знак плюс. Потом просто в начале формулы вставить "=" и нажать Enter.
2. Чуть менее быстрый способ. Скопировать ячейку/всю колонку с такими ячейками в Блокнот, сохранить в нем файл с расширением CSV (для этого выбрать формат "Все файлы" при сохранении), затем в Excel выбрать Данные - Из текста/CSV, указать сохраненный файл, при необходимости указать, каким символом разделены колонки (по умолчанию - запятой), получится таблица, где все числа встанут в отдельных колонках. Просуммировать все колонки. Этот способ имеет смысл, когда надо еще что-нибудь делать с цифрами.
3. Написать макрос, который разобьет текст ячейки на отдельные числа и привязать его к специально добавленной кнопке на ленте или комбинации клавиш. Долго в первый раз, но имеет смысл, если это регулярно повторяющаяся операция.
первые два не подходят, нужно чтобы я в одной ячейке проставлял числа через запятую, а в другой - автоматически выводило сумму. На самом деле, там еще сначала эти числа нужно заменять на другие, а потом уже сумму считать))) С заменой то справился формулой, а с суммой - видимо все таки придется с макросами разбираться, не очень хотелось)) Спасибо
второй пункт (для Exсel 2007 и старше) уже реализован в стандартной функции Данные/Текст по столбцам
У вас документ сохранен в формате с поддержкой макросов?
макрос1()
end function
Уже введено. При вставки текста между началом и концом, компилятор выдает ошибку, при полной замене тоже не получилось.
Посмотрел оформление других макросов, нужно же через sub делать и вызывать функции внутри suba?
просто до этого я ни разу не пробовал макросы.
@Veseliy.4el Я часто использую автоформатирование ячеек по цвету ("Условное форматирование" - "Гистограмма" или аналог. ) Вы не проверяли - может ли описанный вами макрос "переварить" такие автопокрашенные столбцы?
Если гистограмма сплошного окрашивания и она установленного цвета, по идее должна сработать, но я не проверял. Эта формула схожие оттенки выдает одинаковым кодом
А можно от обратного сделать? Т.е. в зависимости от значения в ячейке строка закрашивалась в определенный цвет? Например: в строке Исполнитель ячейка "Иван" и вся строка становится зеленой.
Есть такой способ в Условном форматировании, только надо чтобы ключевые слова были в отдельном столбце, тогда будут закрашиваться в установленные цвета
Но там же не строка, а конкретная ячейка будет закрашиваться?
Закрасится тот диапозон, который вы укажите
Век живи - век учись. Всегда решал подобные задачи при помощи макросов, не знал, что такая функция есть.
Она настолько стара, что любое упоминание о ней выпилено с сайта MS
А можно то же самое, но для гугл таблицы и считать разноцветные ячейки по-горизонтали?
А можно сделать сумму ячеек по ключевому слову в ячейке слева от нее?
#comment_154044691
А если нужно несколько диапозонов собрать?
СУММЕСЛИ(B2:B15;"Доход";C2:C15)
эта формула отбирает ячейки только со значением "Доход"
Как сделать так чтобы в ход шли ячейки с другими значениями но содержащие ключевое слово "доход"? Вышеуказанная формула будет пропускать ячейки со значением "Доход 1", Доход от продажи ручки", "Вася какой то доход яга"
Доход это ключевое слово, заменив которое можно считать любые значения
Вы упорно не понимаете вопроса
В таком случае возьмите и сделайте таблицу с формулой, тогда сами узнаете считает она или нет, у меня нет необходимости специально для вас проверять ее.
А я не спрашивал работает формула или нет, я утверждал что мою задачу она не решит. Вопрос в том какая формула решит. Вообщем выкуси
Ко мне какие претензии? Я разве должен решать твои проблемы? Попрошу не хамить мне, ты спросил, я пытался помочь, не помогло, Гугл тебе в помощь.
Я обычно просто фильтрую по цвету и снижу ставлю сумму.
Посмотрите мой предыдущий пост формула =СУММЕСЛИ(B2:B15;"Доход";C2:C15) слово доход замените на своё
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.
стырено вот отсюда