» Excel FAQ (часть 5)

InSe0F

Цитата:

Можно такое реализовать на встроенных фукнциях или как это сделать на макросах?

При условии предварительно отсортированных столбцов можно реализовать с использованием встроенных функций СМЕЩ(), ПОИСКПОЗ(), ПРОСМОТР(), ИНДЕКС(). Можно и на макросах, используя встроенные механизм автофильтра Excel.

Цитата:

сам же ответил на вопрос =)))
=МИН(A4:L8)
=СРЗНАЧ(A4:L8)
=МАКС(A4:L8)

Undaster, MAGNet, спасибо. А где можно посмотреть соответствие русских команд эксцела английским ?

Niiks, файл FUNCS.XLS - искать в папке установки офиса

Цитата:

не очень ясна суть вопроса. можно более подробно? лучше на примере =)

Столбец 1 Столбец 2 Столбец 3 Столбец 4

InSe0F

Цитата:

Например надо вытащить значение Столбца 3, при условии что, Столбец 4 = 12, а Столбец 1 = 9

=СУММПРОИЗВ((D1:D4=12)*(A1:A4=9)*C1:C4)

но, если ни одна строка не удовлетворяет предъявляемым условиям, такая формула вернёт 0, а если эти условия окажутся верными для нескольких строк, тогда значения в столбце 3 просуммируются в тех строках, для которых условия окажутся истинными.

Отлично! а как теперь сделать тоже самое но только если у меня во всех ячейках текст?

InSe0F
Если бы текст был только в условиях, тогда всё просто, (например, в 4-м столбце ищем слово "текст" - (D1:D4="текст")), а вот как вернуть искомое текстовое значение по нескольким критериям поиска, что то никак не соображу. Обычно в excel как то больше с числами приходится работать.
По одному критерию - элементарно, функцией ВПР или комбинацией ИНДЕКС/ПОИСКПОЗ.
Но решение обязательно должно быть, просто у меня сегодня голова плохо соображает.

Функция БИЗВЛЕЧЬ справляется с подобной задачей, но в этой функции не привычно задаются необходимые условия. (Можно почитать справку Excel по этой функции).

InSe0F
Давайте пример в виде файла Эксель, напишем формулу.

InSe0F,
Попробуй формулу:
=ИНДЕКС($C$3:$C$12;ПОИСКПОЗ(A1&B1;$A$3:$A$12&$B$3:$B$12;0))

здесь:
столбец C - содержит искомые значения
столбцы A и B - содержать "измерения"
в А1 и B1 - значения "фильтры"

Ввод формулы завершить нажатием Ctrl + Shift + Enter (это формула массива)

P.S. Смысл формулы в поиске по "ключу" - соединению значений измерений, будет не корректно отрабатывать случаи, подобные этому
A B C "ключ"

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

Побаловался с БИЗВЛЕЧ пока не получается, не понятно она работает как то

Вот файл - надо в тСтуденты в поле Наставник, вытащить наставника (тНаставники) с кафедры студента, который при этом должен иметь роли "Преподаватель" и "Наставник"

файл http://d.pr/T203

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

БИЗВЛЕЧЬ вернёт ошибку, если в результате выборки будет более одной строки - я так понимаю, что это не твой вариант.

Undaster
ну да) твой способ как оказывается работает)))
Может как вариант макрос-функцию написать, полезная штука-то)?

InSe0F, я не думаю что макрос даст выигрыш в производительности — скорее наоборот, впрочем я не "гуру" конечно, чтобы моё мнение принимать как единственно верное. Если размерность данных достаточно большая и "виртуальный" ключ будет слишком сильно "пожирать" ресурсы, то возможно стоит попробовать переписать формулу через ПОИСКПОЗ(); ИНДЕКС(); СМЕЩ().

InSe0F

=ЕСЛИОШИБКА(ПРОСМОТР(2;1/(([@Кафедра]=тНаставники[Кафедра])*(тНаставники[Роль 1]="преподаватель")*(тНаставники[Роль 2]="наставник"));тНаставники[Преподаватель]);"")

В ячейку D2 листа тСтуденты.

Undaster

Цитата:

ПОИСКПОЗ(); ИНДЕКС(); СМЕЩ().

а как? я пробовал не совсем понимаю как найти по 3ум столбцам с отборами нужный, а если их больше - уже цикл получается(

kimtan
Тоже работает, только можно пояснить - почему "1/" - чем обусловлен данный оператор

InSe0F

Через ВПР (на 5 символов длиннее , чем у kimtan):

=ЕСЛИОШИБКА(ВПР(СУММПРОИЗВ((тНаставники[Кафедра]=[@Кафедра])*(тНаставники[Роль 1]="Преподаватель")*(тНаставники[Роль 2]="Наставник")*тНаставники[№]);тНаставники;3;0);"")

В ячейку D2 листа тСтуденты.

Вопрос к kimtan
Что такое 1/(...) - это понятно, - если все условия выполняются, получается 1/1=1, если нет - 1/0=0 (стоп! на ноль же нельзя делить?)
но читаю справку по функции ПРОСМОТР, и не совсем понимаю логику работы Вашей формулы.
Почему первый параметр - 2? , да и со вторым параметром 1 или 0 тоже нет ясности,
в справке по этой функции первый параметр - Искомое_значение, второй - Просматриваемый_вектор.

Excel 2010.
Как добиться, чтобы при запуске окно приложения разворачивалось на весь экран?

mrdime
Если запускаете Excel через ярлык, то в свойствах ярлыка выставить Окно - Развернутое на весь экран. Если двойным щелчком мыши по файлу *.xlsx (или другим файлам, созданным в Excel), тогда Excel будет открываться в том виде, в каком происходило последнее сохранение этого файла.

VicKos
Работаю всегда в полностью развернутом окне, соответственно сохраняю файлы в том же виде. Двойным щелчком они все-равно открываются не на весь экран.
Поигрался со свойствами ярлыка на Рабочем столе: не зависимо от выставляемых параметров: нормальное окно, свернутое, развернутое - Excel запускается неизменно развернутым только на пол-экрана.
Т.е. проблема пока остается нерешенной.

mrdime
Возможно, установлены какие нибудь надстройки к Excel, или при запуске выполняется код из Личной книги макросов.
Почитайте forum.oszone.net/post-1536680.html , там подобную проблему обсуждали.

VicKos
Спасибо большое! Сам пока "рылся" в сети в поисках ответа наткнулся на эту статью.
Проблема решена!
У меня действительно есть личная книга макросов, которая автоматом запускается с Excel. В указанной Вами статье дальше есть ссылка на сайт Майкрософт, где народ утверждает, что это баг 2010 Excel. Если в автозапуске
C:\Users\<login name>\AppData\Roaming\Microsoft\Excel\XLSTART\
либо в
C:\Program Files (x86)\Microsoft Office\Office14\XLSTART\
есть PERSONAL.XLSB, то сабж ни при каких обстоятельствах не запустится в развернутом окне. Единственное пока (надеюсь до выхода SP) решение, это прописать в ThisWorkbook такой код:
Private Sub Workbook_Open()
Application.WindowState = xlMaximized
End Sub
Желаю всем удачи!

InSe0F
VicKos
Вот нашел тему на соседнем форуме, там ребята, довольно подробно расписали работу подобного алгоритма (у меня лучше не получится ) http://www.planetaexcel.ru/forum.php?thread_id=10983
Там же можно найти еще ссылки для дальнейшего развития этой темы.

В Excel 2003 созданную диаграмму "поверхность" можно взять за "угол" и вращать.
В 2007 и 2010 не получается, нет "углов". Как вращать?

lucky_Luk
Правый клик в диаграмме и выбрать "Поворот объемной фигуры"

jclawe


Цитата:

Правый клик в диаграмме и выбрать "Поворот объемной фигуры"

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

Здравствуйте, скажу сразу, в MS Excel 2003 не силён, в связи с этим решил попросить помощи у специалистов в этой области.
Имеется список, например, такой:

Столбец А Столбец В Столбец С
Иванов 1987 машинист
Петров 1980 столяр
Сидоров 1965 маляр
Мухоморов 1971 завхоз
Грибоедов 1968 директор

необходимо по данным из столбца В определить возраст каждого, подсчитать количество людей, подходящих для след.интервалов возрастов 20-30 30-40 40-50 и вывести кол-во на экран.

т.е. по примеру будет:
20-30 30-40 40-50
1 2 2

Есть набор точек с угловыми величинами или набор участков с начальным и конечным угловым значением.

Можно ли в Excel вывести это на окружность в соответствии со значением угла?

Puks
Могу предложить такой вариант: http://webfile.ru/5264679
serg3001
Легко, только файл/пример.xls вместо Вас делать не буду...

Подскажите, как можно перенести настройки условного форматирования (для разных колонок и строк они, естественно, разные) из одного документа в другой. Понимаю, что вопрос детский, но в справке ответа на нашёл.
Спасибо!

kimtan
сделал файл с примером, вот Ссылка
Премного благодарен за помощь.