Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Передача цвета ячеек диапазона в массив
 
#1
07.12.2015 20:18:42
Добрый вечер, форумчане!

Третий день бьюсь над задачей, видимо, пора уже сдаться и попросить совета у вас.

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

Пробовал такой код (знаю, что ерунда и он неверный):
Код
Function cellColor(targetRange As Range)
    Dim cArr As Variant
    For Each cArr In targetRange
        cArr = targetRange.Interior.ColorIndex
    Next
    cellColor = cArr
End Function
И еще множество его вариантов с переменными для сохранения массива, результатов они не дали, кроме вывода цветового индекса одной выбранной ячейки.

Буду признателен за помощь!
Изменено: johnnyr8891 - 07.12.2015 20:22:30
 
 
 
#2
07.12.2015 20:23:06
Перебрать все ячейки диапазона.
 
 
 
#3
07.12.2015 20:27:43
Да, я в том же направлении думаю. У меня не получается реализовать это.
 
 
 
#4
07.12.2015 20:50:05
Цитата
johnnyr8891 написал: Требуется получить численный массив из индексов цветов ячеек выбранного диапазона как результат работы функции
так вы и сформируйте массив, если всё-таки он вам нужен...
Код
'для одномерного массива
Function cellColor(targetRange As Range) As Variant
    Dim x As Variant
    ReDim x(1 To targetRange.Cells.Count)
    For i = 1 To targetRange.Cells.Count
        x(i) = targetRange.Cells(i, 1).Interior.ColorIndex
    Next
    cellColor = x
End Function
а выгружать массив как будете? я не знаю... но нажмите слева от строки формулы fx и увидите результат - массив...
Изменено: JeyCi - 07.12.2015 22:18:16 (ИСПРАВЛЕН КОД)
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#5
07.12.2015 21:14:31
Пожалуй, это самый быстрый верный ответ, который я когда-либо получал на форумах. Огромное вам спасибо! Сразу увидел весь провал своего опыта в VBA  :) До вашего варианта я бы еще очень долго думал.
 
 
 
#6
07.12.2015 21:18:40
создавая функцию, не забывайте указывать, какой тип данных ей вернуть... As Variant я добавила... и массив (если он нужен), действительно, создаётся через Redim...
Изменено: JeyCi - 07.12.2015 21:20:49
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#7
07.12.2015 21:19:15
Цитата
JeyCi написал: а выгружать массив как будете?
Очень просто - вставить функцию в диапазон как формулу массива. Так же работают стандартные функции МУМНОЖ, ЛИНЕЙН и т.д.
 
 
 
#8
07.12.2015 21:20:31
Цитата
Казанский написал: вставить функцию в диапазон как формулу массива
что-то не работает так... а остальное попробуйте
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#9
07.12.2015 21:28:31
Цитата
JeyCi написал: создавая функцию, не забывайте указывать, какой тип данных ей вернуть...
Спасибо за советы! Буду теперь осваивать.

Цитата
Казанский написал: вставить функцию в диапазон как формулу массива
Да, у меня тоже не срабатывает, только по F9 вывод массива.
 
 
 
#10
07.12.2015 21:38:26
Массив в первой функции формируется горизонтальный. Поэтому и виден только по F9, т.к. уверен - никому в голову не пришло попробовать вывести функцию как формулу массива в строку: A2:H2
Но раз уж такие проблемы:
Код
Function cellColor(targetRange As Range) As Variant
    Dim x, lr As Long, lc As Long
    ReDim x(1 To targetRange.Rows.Count, 1 To targetRange.Columns.Count)
    For lr = 1 To targetRange.Rows.Count
        For lc = 1 To targetRange.Columns.Count
            x(lr, lc) = Cells(lr, lc).Interior.ColorIndex
        Next
    Next
    cellColor = x
End Function

Будет без доп.манипуляций работать как формула массива для любого диапазона(особо не увлекаться большими только).
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#11
07.12.2015 21:45:06
Цитата
johnnyr8891 написал: Да, у меня тоже не срабатывает
можно вывести так
=ИНДЕКС(cellColor(A1:A8);СТРОКА(A1:A8))
Цитата
The_Prist написал: Но раз уж такие проблемы:
спасибо за мудрый совет  8)
P.S.
ой Казанский, я думала то был ответ от ТС #7... поэтому и предложила попробовать самому, т.к. сама ещё не совсем понимала зачем ТСу этот массив и что он будет с ним делать... всё ок - всё попробовали сами  :)
Изменено: JeyCi - 07.12.2015 21:50:26
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#12
07.12.2015 21:51:56
The_Prist,
Дима, поправь x(lr, lc) = targetRange.Cells(lr, lc).Interior.ColorIndex , а это сообщение удали
 
 
 
#13
07.12.2015 21:54:12
The_Prist, да, вы правы.

Заметил, что 2 варианта кода работают не совсем корректно. Если диапазон начинается с 1 ячейки столбца, то все отлично, однако если задать произвольный диапазон, например, А3:А6, то результатом работы функции будут коды ячеек А1:А4. См. прикрепленный файл.
Изменено: johnnyr8891 - 07.12.2015 21:54:32
 
 
 
#14
07.12.2015 21:58:34
Цитата
Казанский написал: x(lr, lc) = targetRange.Cells(lr, lc).Interior.ColorIndex
Попробовал заменить, теперь функция отрабатывает произвольный диапазон в столбце. Предыдущий мой пост (#13) уже не актуален.

The_Prist, JeyCi, Казанский, огромнейшее человеческое спасибо!
 
 
 
#15
07.12.2015 22:02:24
Цитата
Казанский написал: Дима, поправь
Спасибо. Но думаю правильно будет выложить правильный код в новом сообщении. А хронология сообщений и ошибок пусть сохранится.
Код
Function cellColor(targetRange As Range) As Variant
    Dim x, lr As Long, lc As Long
    ReDim x(1 To targetRange.Rows.Count, 1 To targetRange.Columns.Count)
    For lr = 1 To targetRange.Rows.Count
        For lc = 1 To targetRange.Columns.Count
            x(lr, lc) = targetRange.Cells(lr, lc).Interior.ColorIndex
        Next
    Next
    cellColor = x
End Function
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#16
07.12.2015 22:16:35
Цитата
johnnyr8891 написал: Предыдущий мой пост (#13) уже не актуален.
я тоже так думаю  ;)
чуток подкорректировала код (и исправила выше)
Код
x(i) = targetRange.Cells(i, 1).Interior.ColorIndex
и формулу можно попроще (чтобы тянуть вниз) =ИНДЕКС(cellColor($A$3:$A$6);СТРОКА(A1))
Изменено: JeyCi - 07.12.2015 22:21:37
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#17
08.12.2015 15:00:26
JeyCi, еще раз – большое спасибо!

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

Имеются 2 столбца: 1 заполнен цветными ячейками и числовыми значениями этих ячеек, 2 — текстовыми примечаниями ("Действие 1", "Действие 2"). Необходимо найти сумму ячеек на основе цвета и текстового примечания, т.е., всего 4 суммы: 2 для красных и Действия 1, 2 — для оранжевых и Действия 2.

Искомая функция нужна была для возвращения диапазона значений индексов цвета выбранных ячеек, чтобы в дальнейшем при помощи функции =SUMIFS, критерия по индексу цвета, диапазону текстовых примечаний и критерия самого примечания вычислить сумму значений ячеек.
Я пробовал вычислить эти суммы, задействовав макросы от JeyCi, и The_Prist, и переименовав функции в них как cCol_JC и cCol_TP для удобства, но всегда получал ошибку #VALUE!

Прилагаю скриншоты и сам файл.
Скрытый текст
 
 
 
#18
08.12.2015 16:07:37
johnnyr8891, читайте внимательно: Criteria_range1: is the range of cells you want evaluated...
Этот аргумент может быть только диапазоном ячеек. А не массивом.
Используйте СУММПРОИЗВ (SumProduct), у нее аргументы могут быть какие угодно.
 
 
 
#19
08.12.2015 16:52:18
Казанский, спасибо большое! Здорово, когда мысли в нужное русло направляются! =SUMPRODUCT помог решить задачу, выкладываю файл с решением. Функция, предложенная JeyCi, почему-то не отрабатывает, с функцией от The_Prist, все в порядке.

Цитата
Казанский написал:
Criteria_range1: is the range of cells
Да, я обращал на это внимание и не знал, как поступить. Но, раз уж вопрос решен другим способом, то теперь чисто спортивный интерес — можно ли массив преобразовать в диапазон для подстановки в виде аргумента в функцию =SUMIFS?
 
 
 
#20
08.12.2015 17:26:19
Цитата
johnnyr8891 написал:  можно ли массив преобразовать в диапазон
Только предварительно выгрузив его на лист.
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#21
08.12.2015 17:51:07
The_Prist, спасибо! Для моей задачи это не совсем удобный способ, но я попробую на досуге для общего развития.
 
 
 
#22
08.12.2015 20:37:40
Цитата
johnnyr8891 написал: Функция, предложенная JeyCi, почему-то не отрабатывает
потому что
Цитата
The_Prist написал: Массив в первой функции формируется горизонтальный
значит для вашей СУММПРОИЗВ (в которой все остальные массивы вертикальные!) - его надо транспонировать...
p.s.
формула у вас не совсем оптимальная - вы не можете её растянуть вниз (надо руками через каждую ячейку править условия)... в следующий раз продумывайте заранее структуру представления данных, её оптимальность и удобство пользования (вам же потом будет легче)... и информируйте о цели поставленной задачи... чтобы не получалось пальцем в небо вам отвечать... хотя и из ответов по ветке можно экспериментировать с тем, что получилось (используя соответствующий Логичный подход... а не по аналогии)... успехов вам (исследуйте возможности и будете получать нужные вам и созданные вами решения)
Изменено: JeyCi - 08.12.2015 20:39:50
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#23
08.12.2015 20:50:02
Цитата
johnnyr8891 написал: можно ли массив преобразовать в диапазон для подстановки в виде аргумента в функцию
?? зачем тогда формировать массив, чтобы потом обратно его превращать в диапазон - сделайте доп. столбец и напишите функцию для определения цвета Одной ячейки... растяните формулу... а значения доп столбца используйте хоть как массив, хоть как диапазон...
Отсюда
Код
Function цвет(cl As Range)
    цвет = cl.Interior.Color
End Function
Изменено: JeyCi - 08.12.2015 21:38:32
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#24
08.12.2015 21:02:33
Кстати, если критерия только два - значение ячейки и цвет, то можно и готовые решения применить:
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#25
08.12.2015 21:36:00
или такой вариант
Функция Получить.Ячейку
чтобы не гадать на кофейной гуще, кто вам отвечает и после этого не совершать кучу ошибок - обратитесь к собеседнику на ВЫ - ответ на ваш вопрос получите - а остальное вас не касается (п.п.п. на форумах)
 
 
 
#26
09.12.2015 00:12:44
JeyCi, ввиду того, что кол-во столбцов с цветными ячейками и текстовыми критериями планируется гораздо больше, чем в исходном тестовом файле, то вводить дополнительные столбцы с просчетом протягиванием функции определения цветового индекса будет неудобным. Мне как раз требовался расчет нужных значений по одной формуле в одной ячейке. Согласен, что конечная формула не выглядит лаконичной, но на данный момент удовлетворяет всем ожиданиям. Извиняюсь, если ввел в заблуждение на каком-то этапе  :) И большое спасибо за дельные советы, акценты на нюансы и последнюю ссылку!  ;)  

The_Prist, благодарю за ссылки! Со временем кол-во критериев будет больше. Но, под иную похожую задачу — очень кстати!
Изменено: johnnyr8891 - 09.12.2015 00:15:54
 
 
 
Страницы: 1
Читают тему
Наверх