Помогите с Excel, плиз

[Инквизитор]

Плиз хелп ми! Моих обрывочных знаний не хватает, а экселевский хелп выдает все что угодно, только не то что надо.

 

Задача:

Дана таблица из двух столбцов. В одном столбце числа, в другом - категории. Что-то типа пары "ключ-значение", только "ключи" могут быть одинаковыми.

Надо просуммировать отдельно значения по каждому из ключей и вывести это в отсортированном виде.

 

Примерное мое представление этого дела - на рисунке:

 

[Nikolas]

суммесли

 

есть такая функция. Подойдёт?

 

Ща формулу напишу...

 

=СУММЕСЛИ(C$3$:C$32$;E$3;B$3$:B$32$)

 

поместите это формулу в ячейку F3 и прокопируйте её (протяните) на все ячейки до F16

 

Это для суммирования.... для вывода уникальных значений формулой - надо подумать...

[Гость bred]

да.

sumif(диапазон ячеек с условием; само условие; диапазон ячеек для суммирования)

напр, sumif(B$2:B$32; 3; C$2:C$32)

вместо "3" может стоять и адрес ячейки.

[Гость dwor]

Nikolas, +1 :)

 

Когда-то попалась книга "Excel для чайников". Подумал, что будут там тупые советы для самых тупых.

Пролистал - затянуло. В книге оказалось много дельных и новых вещей. Во время учебы здорово помогло.

[Nikolas]

dwor, я эксель учил по книге "MS Office 97 для профессионалов" издательства QWE. Вот только компьютера тогда ещё не было ни у кого из моего окружения, так что мне просто приходилось конспектировать в тетрадку на будущее, когда смогу попробовать за компом... вот до сих пор помню :)

[Инквизитор]

Спасибо всем!

 

Меня тут просветили - вставка сводной таблицы, указать диапазон. Сработало на ура.

СУММЕСЛИ - буду знать.

 

Самое забавное, что найти требуемое через штатный хелп - нетривиальная задача...

[Batiy]

Тоже изучал по "Excel для чайников", но так объяснить, как Nikolas, не смог бы. Очевидно, для этого необходимо учиться по профессиональной литературе.

[Nikolas]

Тоже изучал по "Excel для чайников", но так объяснить, как Nikolas, не смог бы. Очевидно, для этого необходимо учиться по профессиональной литературе.

а что я не так сказал?

 

ЗЫ. Учился по тому - что нашёл у друга. Выбора как бы особо не было в литературе.

[Инквизитор]

А теперь вопрос на засыпку:

 

можно ли как-то задать в качестве условия выбора формат ячейки и/или текста в ней?

[Nikolas]

Вам поможет функция ЯЧЕЙКА

 

 

 

Excel > Справочник по функциям > Информационные функции Функция ЯЧЕЙКА

blueup_CLV.gifПоказать все

bluedrop_CLV.gifСкрыть все

В этой статье описан синтаксис формулы и использование функции (Функция. Стандартная формула, которая возвращает результат выполнения определенных действий над значениями, выступающими в качестве аргументов. Функции позволяют упростить формулы в ячейках листа, особенно, если они длинные или сложные.) ЯЧЕЙКА в Microsoft Office Excel. Дополнительные сведения о форматировании данных в ячейках и применении стилей ячеек см. в разделе См. также.

 

 

Описание

Функция ЯЧЕЙКА возвращает сведения о форматировании, адресе или содержимом ячейки. Например, если перед тем, как выполнить вычисления с ячейкой, необходимо удостовериться в том, что она содержит числовое значение, а не текст, можно использовать следующую формулу:

 

=ЕСЛИ(ЯЧЕЙКА("тип", A1) = "v"; A1 * 2; 0)

 

Эта формула вычисляет произведение A1*2, только если в ячейке A1 содержится числовое значение, и возвращает значение 0, если в ячейке A1 содержится текст или она пустая.

 

 

Синтаксис

ЯЧЕЙКА(тип_сведений, [ссылка]) Функция ЯЧЕЙКА имеет аргументы (Аргумент. Значение, предоставляющее информацию для действия, события, метода, свойства, функции или процедуры.), указанные ниже.

 

• Тип_сведений. Обязательный аргумент. Текстовое значение, задающее требуемый тип сведений о ячейке. В приведенном ниже списке указаны возможные значения аргумента тип_сведений и соответствующие результаты. Тип_сведений Возвращаемое значение "адрес" Ссылка на первую ячейку в аргументе «ссылка» в виде текстовой строки. "столбец" Номер столбца ячейки в аргументе «ссылка». "цвет" 1, если ячейка изменяет цвет при выводе отрицательных значений; во всех остальных случаях — 0 (ноль). "содержимое" Значение левой верхней ячейки в ссылке; не формула. "имяфайла" Имя файла (включая полный путь), содержащего ссылку, в виде текстовой строки. Если лист, содержащий ссылку, еще не был сохранен, возвращается пустая строка (""). "формат" Текстовое значение, соответствующее числовому формату ячейки. Значения для различных форматов показаны ниже в таблице. Если ячейка изменяет цвет при выводе отрицательных значений, в конце текстового значения добавляется «-». Если положительные или все числа отображаются в круглых скобках, в конце текстового значения добавляется «()». "скобки" 1, если положительные или все числа отображаются в круглых скобках; во всех остальных случаях — 0. "префикс" Текстовое значение, соответствующее префиксу метки ячейки. Апостроф (') соответствует тексту, выровненному влево, кавычки (") — тексту, выровненному вправо, знак крышки (^) — тексту, выровненному по центру, обратная косая черта (\) — тексту с заполнением, пустой текст ("") — любому другому содержимому ячейки. "защита" 0, если ячейка разблокирована, и 1, если ячейка заблокирована. "строка" Номер строки ячейки в аргументе «ссылка». "тип" Текстовое значение, соответствующее типу данных в ячейке. Значение «b» соответствует пустой ячейке, «l» — текстовой константе в ячейке, «v» — любому другому значению. "ширина" Ширина столбца ячейки, округленная до целого числа. Единица измерения равна ширине одного знака для шрифта стандартного размера.
• Ссылка. Необязательный аргумент. Ячейка, сведения о которой требуется получить. Если этот аргумент опущен, сведения, указанные в аргументе тип_сведений, возвращаются для последней измененной ячейки. Если аргумент ссылки указывает на диапазон ячеек, функция ЯЧЕЙКА возвращает сведения только для левой верхней ячейки диапазона.

Коды форматов функции ЯЧЕЙКА

В приведенном ниже списке описаны текстовые значения, возвращаемые функцией ЯЧЕЙКА, если в качестве аргумента тип_сведений указано значение "формат", а аргумент ссылки указывает на ячейку, отформатированную с использованием встроенного числового формата.

 

Формат Microsoft Excel Значение, возвращаемое функцией ЯЧЕЙКА Общий "G" 0 "F0" # ##0 ",0" 0,00 "F2" # ##0,00 ",2" $# ##0_);($# ##0) "C0" $# ##0_);[Красный]($# ##0) "C0-" $# ##0,00_);($# ##0,00) "C2" $# ##0,00_);[Красный]($# ##0,00) "C2-" 0% "P0" 0,00% "P2" 0,00E+00 "S2" # ?/? или # ??/?? "G" д.М.гг или дд.ММ.гг Ч:мм или дд.ММ.гг "D4" Д МММ ГГ или ДД МММ ГГ "D1" д.м, или дд.ммм, или Д МММ "D2" ммм.гг, ммм.гггг, МММ ГГ или МММ ГГГГ "D3" дд.мм "D5" ч:мм AM/PM "D7" ч:мм:сс AM/PM "D6" ч:мм "D9" ч:мм:сс "D8" Примечание. Если аргумент тип_сведений функции ЯЧЕЙКА имеет значение "формат", а формат ячейки был изменен, для обновления значения функции ЯЧЕЙКА необходимо пересчитать лист.

 

 

Пример

Чтобы лучше понять этот пример, скопируйте его на пустой лист.

 

blueup_CLV.gifКопирование примера

 

1. Выделите пример в этой статье. Важно. Не выделяйте заголовок строки или столбца.
    
   ExcelExampleSelection_ZA10243433.gif
    
   Выделение примера в справке.
2. Нажмите сочетание клавиш CTRL+C.
3. Создайте пустую книгу или лист в приложении Excel.
4. На листе выделите ячейку A1 и нажмите сочетание клавиш CTRL+V. Важно. Для правильной демонстрации примера необходимо вставить его в ячейку A1 листа.
5. Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите сочетание клавиш CTRL+` (тупое ударение) или на вкладке Формулы в группе Проверить формулы нажмите кнопку Показывать формулы.

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

 

1 2 3 4 5 6 7 8 A B C Данные

 

5 мар

 

ИТОГИ

 

Формула Описание Результат =ЯЧЕЙКА("строка";A20) Номер строки ячейки A20 20 =ЯЧЕЙКА("формат"; A2) Код формата ячейки A2 D2 (Д МММ) =ЯЧЕЙКА("содержимое"; A3) Содержимое ячейки A3 ИТОГИ =ЯЧЕЙКА("тип"; A2) Тип данных ячейки A2 v (значение) xl защищена ли ячейка имя файла текущей книги определить получить номер строки для справки содержимое левой верхней ячейки тип данных содержимого ячейки

 

 

 

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

 

Хотя что Вы имеете в виду под форматом? Тип данных в ячейке или цвет текста?

[Инквизитор]

Хотя что Вы имеете в виду под форматом? Тип данных в ячейке или цвет текста?

 

Спасибо!

 

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

[forum]

Я всегда сдесь ответы ищу. И таки нахожу, помогают люди :)

[Nikolas]

Инквизитор, ну как стандартной формулой это сделать я не знаю... могу макрос написать (пользовательскую формулу). Но тлогда мне опять таки надо более точное задание.

[Инквизитор]

forum, спасибо за ссылку.

 

Инквизитор, ну как стандартной формулой это сделать я не знаю... могу макрос написать (пользовательскую формулу). Но тлогда мне опять таки надо более точное задание.

 

 

 

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

 

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

[Nikolas]

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

сейчас макрос сварганю.

[Nikolas]

Предупреждаю - сделано на коленке. Только для выполнения это задачи. Если надо что-то более универсальное - могу доработать.

 

Sub color_sum()
For a = 1 To 10
If Cells(a, 1).Interior.ColorIndex = 6 Then
   	yel = yel + Cells(a, 1)
ElseIf Cells(a, 1).Interior.ColorIndex = 3 Then
   	red = red + Cells(a, 1)
ElseIf Cells(a, 1).Interior.ColorIndex = 4 Then
   	green = green + Cells(a, 1)
ElseIf Cells(a, 1).Interior.ColorIndex = xlNone Or Cells(a, 1).Interior.ColorIndex = 2 Then
   	white = white + Cells(a, 1)
End If

Cells(1, 3) = "Yellow ="
Cells(1, 4) = yel
Cells(2, 3) = "Red ="
Cells(2, 4) = red
Cells(3, 3) = "Green ="
Cells(3, 4) = green
Cells(4, 3) = "White ="
Cells(4, 4) = white
Next a

End Sub

 

Чо делает: суммирует чиста с ячеек с А1 по А10 по цветам подсветки ячейки Жёлтый, Красный, Зелёный, Белый/без заливки

выглядит примерно так

[Инквизитор]

Nikolas, мегаблагодарствую!

 

Макросы - это сурьезно.

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

Погуглю при случае.

[Nikolas]

Инквизитор, та не за что.

 

а) Вот не знаю :( Мне проще макрос написать

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

[Инквизитор]

Не, не надо. Спасибо!