» Excel FAQ (часть 5)

ALeXkRU
спасибо)

Подскажите, пожалуйста, как можно отсортировать в экселе прибыль по городам.Ь. е. сделать такую моду. Например

общая прибыль город
50 000 питер
40 000 москва
30 000 ростов
20 000 питер
10 000 новгород

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


общая прибыль город
50 000 питер
20 000 питер
40 000 москва
30 000 ростов
10 000 новгород
и так далее.
Просто обычный фильтр, он просто обирает или включает город, это не то.
на всяктй случай файл прикрепляю

http://rghost.ru/51773245

Цитата:

как можно отсортировать

а почему не использовать
Данные - Сортировка?

Voo2
ну, окончательная цель это чтобы было так
например, петербург встречается 20 раз, суммарные прибыль от этих 20 человек 10 000 рублей к примеру.
Я так имел ввиду в конечном итоге.

Futurism - Тогда легче всего будет PivotTable, там можно и консолидацию и сортировку произвести.

Цитата:

PivotTable

Это программа наподобие Экселя? Меня программа по легче, но работающая с файлами Экселя интересует. Есть ли такая? Комп. часто на пределе и такие файлы порой не открывает или откроет, но работать не возможно. У меня задачки довольно простые.

Как т статью читал, для IPAD ов сделали, скоро должна или уже появилась. Легка и позволяет редактировать такие файлы. Может приручил бы ее и для компов.

Aktaf - Это не программа наподобие Excel, это встроенная часть Excel(2010-2013): Insert->PivotTable
и Access(2010)

Приветствую коллеги, подскажите как можно реализовать в Excel 2010, такую штуку.
Имеется таблица из 2 столбцов и 3 строк.
Столбец А1 это название, столбец А2 значения.
Обязательно для заполнения только 1 значение.
Нужно сделать так, чтоб при заполнении строки из столбца а2 текстом (произвольным текстом) выдавалось значение 5, а остальные ячейки (а1 и а3) 0.
Если заполнена строка а3, то значение 7.

Как это сделать?
https://docs.google.com/spreadsheet/ccc?key=0Auaxq_93t4c3dGpHZkdSZ2NXTERucnhEYjRmbGRGWGc&usp=sharing

k3NGuru
например, для второй строки в ячейку С2:

Код: =ЕСЛИ(ЕПУСТО(B2);"";5)

ALeXkRU
Спасибо большое

Подскажите плиз формулку.
если а1= v, то g2=3, если а2=V то g3=2, если а3=v то g4=3 , если а4=V, то к g3 прибавить 3, , а если а5=v то k g4 прибавить 2.

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

Из того, что смог догадаться:

в G2
Код: =ЕСЛИ(СОВПАД($A$1;"v");3;0)

ALeXkRU Все верно поняли. Тут даже пример выкладывать не надо. Формулы простые, а синтаксиса я не знаю
А как перевести в экселевский язык эту формулу
Если а1 имеет значения в диапазоне от 1 до10, то b1= с1, а если а1 имеет значения в диапазоне от 10-20 , то b2=c2.

Futurism
а если а1 = 10? тогда что? в формулах неопределённостей быть не должно!

Добавлено:

Цитата:

от 1 до10

включительно? или часть включается, а часть нет..

Цитата:

от 10-20

до? включительно?

Добавлено:

Цитата:

Тут даже пример выкладывать не надо.

угу-угу.. только тот, кто решит этим заняться, должен будет сам вчитываться в описание, расставлять значения по ячейкам.. Придумывать, v и V - это одно и то же? или нет... Что делать если в ячейках вообще ничего не стоит.. и т.д. и т.п...
Вместо того, чтобы взять готовый файл с исходными данными и только решение придумывать..

ЗЫ: вот чуть выше есть вопрос от k3NGuru, так на такой и отвечать приятно

Добавлено:
ЗЫ2:
вот один из вариантов, опять же, основанный чисто-формально на исходных данных

Код: =ЕСЛИ(И((A1>=1);(A1<10));C1;ЕСЛИ(И((A1>10);(A1<20));C2;"вне диапазона"))

Цитата:

включительно? или часть включается, а часть нет..

Вот тут правы не уточнил, только включительно)

Futurism

Цитата:

тут правы не уточнил

так ить, так и не известно, что с 10-кой? она куда входит?

блин, я тороплюсь от 1-10, от 11-20 вот так))
Например, например общие баллы субъекты пишутся в ячейку а1, если там сумма 1-10 вкл, то b1= с1, а если 11-20 вкл, то b2= с2
Под вечер туго соображаю. А интерпретацию завтра давать(

Futurism
лучше то не стало что теперь с после десяти?
Бум считать так: от 1 (вкл) до 10 (вкл) ; больше 10 до 20 (вкл)

Код: =ЕСЛИ(И((A1>=1);(A1<=10));C1;ЕСЛИ(И((A1>10);(A1<=20));C2;"вне диапазона"))

ALeXkRU
как всегда профессионально выполнили.)) Респект)

ALeXkRU
Есть задачка посложнее. А как составить формулу, где интерпретируется как бы 3 значения.
Например после вычетов получилось такие значения
a1=10, b1=5, c1=2 Т.е. то значение, которое больше, то и говорить и выраженности стиля.
а интерпретация идет т.о
Возможные соотношения 3 крайних типов в стиле руководства таковы:

Д-1-1: директивный стиль (например, 10-2-4, 6-3-4, 9-5-4 и т. д.);
1-К-1: коллегиальный стиль (например, 4-9-4, 3-8-4, 5-10-4);
1-1-П: попустительский стиль (например, 3-2-10, 4-3-9 и т. д.).

Если приблизительно одинаково выраженные 2 стиля доминируют над 3-м, это стиль руководства смешанный:

Д-К-1: директивно-коллегиальный (например, 7-8-4, 6-7-3 и т. д.);
1-К-П: коллегиально-попустительский (например, 4-7-9, 4-7-7, 3-6-7 и т. д.);
Д-1-П: директивно-попустительский (например, 8-1-6, 8-3-8, 7-2-9);
Д-К-П: смешанный (например, 2-3-3; 3-3-3; 5-6-6; 10-8-8).


как такую формулу прописать?

Добавлено:
тут значений больше 10 не может быть.

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

Нашел формулу,
http://www.pcreview.co.uk/forums/sumif-months-and-year-t3304250.html

=SUMPRODUCT(--(YEAR(A1:E1)=YEAR(A4)),--(MONTH(A1:E1)=MONTH(A4)),A2:E2)
- на запятые ругается. Сменил на звездочки (в некоторых видел ф-лу с ними)
http://answers.microsoft.com/en-us/office/forum/office_2003-excel/sumif-or-sumproduct-based-upon-month-and-year/01a74be4-a271-48f8-bf35-31457ca27e30
=SUMPRODUCT((MONTH(B1:B100)=2)*(YEAR(B1:100)=2011)*A1:A100)
- аналогично, ругается.

Что я делаю не так?
Офис 2010, английский, точнее, литовский с англ интерфейс паком

Добавлено:
Futurism
в вашем случае, кмк, не сами значения сравнивать стоило б, а их разности. Иначе "примерно равно" не описать. А так - модуль А-В меньше или равно 2 - тогда значение такое-то. Ну и т.д.

bredonosec
а там не точка с запятой разделители?

я и их пробовал. в результате вместо выскакивавшего "неправильная формула" - всего лишь значение #VALUE! в ячейке..

Помогите, пожалуйста советом:

У меня есть excel 2010 книга. В ней пока два листа, возможно будет больше. В первом листе я заношу в ячейки b2-bn Фамилии людей, в ячейки d2-dn - группу к которой этот человек относится (1, 2, 3... n). На втором (а в перспективе и на всех последующих) я в ячейку b2-bn вношу данные из первого листа аналогичной ячейки.
Как сделать так, чтобы на первом листе excel смотрел номер группы и в зависимости от номера на всех остальных листах закрашивал нужным цветом всю строчку с фамилией, взятой из b2-bn первого листа?

Пока искал "программа по легче, но работающая с файлами Экселя интересует", встретился вот такое (вдруг кому понадобиться):

Программное обеспечение Mathematica Link for Excel позволяет проводить сложные математические вычисления или анализ данных в Excel. Система Mathematica, интегрированная в платформу Excel, позволит применять более тысячи дополнительных функций и опций к уравнениям и данным. Пользователи смогут экспортировать большие наборы данных из Excel в систему Mathematica для сложного анализа, а результаты – обратно в электронную таблицу.

Система Mathematica содержит встроенные функции для различных вычислений, благодаря которым операции, требующие в Excel написания макросов, могут быть выполнены в системе Mathematica с помощью нескольких команд.
http://rutracker.org/forum/viewtopic.php?t=3774948

Хотя это не в моих интересах. Я с этой темы узнаю полезное для себя.

55550000
Я не сомневаюсь, что Вы понимаете то что написали, но вот мне пришлось раз пять перечитать, а понимания вопроса так и не возникло.
Первая часть в принципе понятна - на втором и последующих листах в столбце B данные дублируют столбец B с первого листа (вношу данные из первого листа аналогичной ячейки). Их даже вносить не надо, зачем мучиться, смотреть, что там, на первом листе - просто вставить формулы, которые будут брать значения ячеек с первого листа, например в B2 на втором листе формула =Лист1!B2 и "протянуть" её вниз до ячейки Bn.
Посмотреть номер группы на первом листе тоже несложно, с этим должна справиться функция ВПР, например, она нашла, что фамилии Иванов соответствует группа 2. Но вот дальше мне непонятно - каким таким "нужным" цветом стоит закрасить строку с фамилией, взятой из первого листа, даже если мы определили, что Иванов из второй группы?

Кстати, учитывая количество ячеек на листе Excel 2010, полагаю, что закрашивание "всей строки" с фамилией в случае применения условного форматирования ячеек приведёт к существенному росту размера файла и довольно сильному увеличению времени пересчета книги при изменении даже одной ячейки.

уточнение задачи.

Вариант
=SUMPRODUCT(--YEAR(A$1:A$1525)=YEAR(A1532)*--MONTH(A$1:A$1525)=MONTH(A1532)*(C$1:C$1525))
по идее, работает. Точнее, вроде бы работает: не кидает ошибки.. Но! Исключительно если во всем массиве первый столбец исключительно даты.
А если нет? Если пустое место или текст?
Пробовал конструкцию формата
сначала проверка if(isnumber();then;else)

Но! Как её применять ко всему массиву? Если в массиве хоть одна ячейка НЕ дата – функция возвращает false.

Втыкая isnumber в число условий сумпродукта, получаю то же #VALUE

как с ним справиться-то?

bredonosec
примерчик бы, для поковырять

VictorKos


Ээээ старался максимально понятно объяснить. На листе 1 вы правильно поняли - есть фамилия и номер группы (всего 4-6 групп).
На втором листе - фамилия так и заносится (через формулу - не буду де я дублировать постоянно их ), а затем этой фамилии соответствует порядка 300 столбцов. Когда это две три фамилии=строчки, то не сложно, но если их более ста, то это очень сложно.
Мне нужно, чтобы excel посмотрел например в первой строчке стоит иванов, к какой группе он относится на первом листе, например к 3. Значит всю строку иванова на втором листе закрасить например синим.
Следующая строка например петров. Он предположим в 4 группе, значит его строку нужно сделать желтой. И тд. Если я на первом листе меняю группу, например у иванова с 3 на 4, то на втором листе он сразу меняется на желтый.

Вы думаете это сильно замедлит работу?

55550000
Всё же лучше подсветить участок строки в 300 ячеек, чем всю строку (в Excel 2010 - 16384 столбцов, и закрашивание 16 000 пустых ячеек в каждой строке приведет к нерациональному расходованию ресурсов компьютера, так как для описания условного форматирования каждой ячейки Excel вынуждена будет расходовать дополнительную память.
Инструмент "Условное форматирование", который как раз и занимается визуальным выделением ячеек в зависимости от их содержимого, не может сам решать, каким цветом залить строку с Ивановым, нужно применить условное форматирование с использованием формул и описать в формуле условие, например:
=ВПР($B2;группа;2;ЛОЖЬ)=3
где "группа" - имя диапазона на первом листе B2:Dn (можно вместо имени указать диапазон, но вместо Dn указать последнюю ячейку диапазона)
ищет содержимое ячейки B2 в первом столбце диапазона "группа" и возвращает номер группы из найденной строки, но из столбца D, и, если найденный номер равен 3
...
честно говоря, я уже устал всё это писать. Вот если бы Вы подготовили и выложили файл Excel, в котором разместили небольшое количество ваших данных (таблицу на первом листе и несколько фамилий на втором), думаю, что ответ ещё вчера был бы готов. Тогда и возникшие вопросы можно было бы обсуждать более предметно. А описывать то, что есть в справке Excel и много раз обсуждалось на всевозможных тематических форумах, не хочется.