» Excel FAQ (часть 5)

InSe0F

Цитата:

я так полагаю единственный вариант переходить к макросу?

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

InSe0F
Узнаю свою формулу, но помогать Вам не намерен пока не будет нормального примера с объяснениями в виде файла *.xls
Удачи!

Цитата:

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

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


Цитата:

Узнаю свою формулу, но помогать Вам не намерен пока не будет нормального примера с объяснениями в виде файла *.xls

вот

например для Сидоров'а должен выводиться наставник Виненко у которого указана Наставник в "Роль2"

http://d.pr/MiRZ

InSe0F
Вот вам универсальная формула массива:

=ЕСЛИОШИБКА(ПРОСМОТР(2;1/(([@Кафедра]=тНаставники[Кафедра])*(МУМНОЖ(--(тНаставники[[Роль 1]:[Роль 2]]="Наставник");ТРАНСП(СТОЛБЕЦ(тНаставники[[#Заголовки];[Роль 1]:[Роль 2]])^0))>0));тНаставники[Преподаватель]);"")

Ключевые моменты выделил жирным шрифтом.

kimtan
Извините, что вмешиваюсь, но...
Если в таблице тНаставники переместить третью строку с преподавателем Кузнецовым на место первой строки, ваша новая формула вместо Кузнецова выдаёт ассистента Филкина (впрочем, он тоже "наставник", так что не уверен, ошибка ли это). Видимо, ищется последняя, удовлетворяющая критериям поиска, запись в таблице.

Цитата:

Видимо, ищется последняя, удовлетворяющая критериям поиска, запись в таблице.

Именно так формула работала с самого начала, и так продолжает работать



Добавлено:
Для того, чтобы возвращала первую запись, нужно внести небольшие изменения:

=ЕСЛИОШИБКА(ИНДЕКС(тНаставники[Преподаватель];ПОИСКПОЗ(1;1/(([@Кафедра]=тНаставники[Кафедра])*(МУМНОЖ(--(тНаставники[[Роль 1]:[Роль 2]]="Наставник");ТРАНСП(СТОЛБЕЦ(тНаставники[[#Заголовки];[Роль 1]:[Роль 2]])^0))>0));));"")

kimtan
спасибо)

да и если можно немного пояснить работу МУМНОЖ , с двойным отрицанием...

Что конкретно непонятно?
Как работает МУМНОЖ() или для чего в формуле используется бинарное отрицание?

Цитата:

для чего в формуле используется бинарное отрицание

это первое

Цитата:

ТРАНСП(СТОЛБЕЦ(тНаставники[[#Заголовки];[Роль 1]:[Роль 2]])^0))>0))

от сюда я как понял мы массив/матрицу единиц) только не понятно зачем она 1*1 все равно 1...

Бинарное отрицание используется для преобразования массива логических ИСТИНА и ЛОЖЬ, полученных в результате сравнения: (тНаставники[[Роль 1]:[Роль 2]]="Наставник"), в числа, соответственно в 1 и 0, так как МУМНОЖ() понимает только числовые массивы.

СТОЛБЕЦ(тНаставники[[#Заголовки];[Роль 1]:[Роль 2]])^0 возвращает горизонтальный массив состоящий из двух единиц. Транспонируем его в вертикальный массив (требуется для корректной работы во втором аргументе функции МУМНОЖ() - "массив2"). Подробнее про работу функции МУМНОЖ() можно почитать в справке Excel.

1. можно ли как то вставлять примечания к ячейкам из текстовых файлов (чтоб не копипастить)
2. как сделать чтоб размер примечаний автоматически подстраивался под содержимое

NEW_MAKC
2. На примечании ПКМ Формат примечания - Выравнивание - Автоматический размер

1. Думаю только плагином. Т.е. есть набор текстовых файлов, содержимое каждого из которых должно попасть в собственное примечание?

Всем спасибо за помощь, тут возник еще один вопрос, скорее правда по функционалу, но все же.
В 1С есть очень удобная функция под названием "Отбор по текущему значению", ну или что-то этом роде - суть в том, что когда у тебя выделена какая то ячейка, при нажатии на эту волшебную кнопку в текущем столбце просиходит установка фильтрации, где значение фильтра = Значению ячейки
Можно либо подобное реализовать в Excel?
Ибо иногда жутко не удобно, когда необходимо сделать отбор при нахождении в текущей ячейки а самих вариаций отборов очень многи и приходится долго листать список фильтрации для поиска необходимого значения...

InSe0F
а чем не подходит этот вариант?

InSe0F
Или выбросить на панель подсказанную KF121 кнопку (в 2003 еще нет ее),

или сделать свою и назначить на нее процедуру. А внутри процедуры уже упражняться, как душа велит :)

Sub sb_AutoFilter()
ActiveCell.AutoFilter Field:=1, Criteria1:=ActiveCell.Value
End Sub

Цитата:

NEW_MAKC
2. На примечании ПКМ Формат примечания - Выравнивание - Автоматический размер

1. Думаю только плагином. Т.е. есть набор текстовых файлов, содержимое каждого из которых должно попасть в собственное примечание?

блин, пробовал такой вариант (по вопросу 2) - ничего не получалось. сейчас получилось..... спасибо

по вопросу 1 - ну грубо говоря - хочу примечание, нажимаю "добавить примечание" и там функция "добавить из файла" - что то типа этого

Цитата:

хочу примечание, нажимаю "добавить примечание" и там функция "добавить из файла" - что то типа этого

Стандартным интерфейсом это не сделать. Концептуально это будет выглядеть так: на пользовательскую кнопку вешаем процедуру. Из процедуры вызываем диалог, в которм укажем файл с комментарием, читаем его, содержимое загоняем в примечание. Только это ужЕ в смежную тему.

asbo

Цитата:

Или выбросить на панель подсказанную KF121 кнопку (в 2003 еще нет ее),

а как она наызвается в списке команд а то по аналогичному на Фильтр.. я ничего не нашел
вообще пробежался по всем ничего подобного не встретил.. Подскажите мож не там смотрю

Цитата:

а как она наызвается

, я же говорю - нет ее в 2003 :)) А структуру контекстного меню после я не знаю. Внимательно пошариться надо в ПКМ на панели инструментов - Настройки. Я про этот риббон ваще ничего не знаю. И не хочу знать :)

Цитата:

Внимательно пошариться надо в ПКМ на панели инструментов

все обшарил.. http://d.pr/PwkG не нашел или она как то называется по другому... (

Добавлено:

Цитата:

Sub sb_AutoFilter()
ActiveCell.AutoFilter Field:=1, Criteria1:=ActiveCell.Value
End Sub

а куда положить его чтобы он был автоматом во всех книгах?

asbo
ИМХО надо поправить
Sub sb_AutoFilter()
ActiveCell.AutoFilter Field:=ActiveCell.Column, Criteria1:=ActiveCell.Value
End Sub

InSe0F

Цитата:

а куда положить его чтобы он был автоматом во всех книгах?

Э-э... батенька... так дела не делаются :) Куда положить... Ну не на Альфу же. Ты номер своей планеты в центуре знаешь?

Создается новая книга, в нее помещается этот код и сохраняется, как надстройка (xla). Файл кладется в заветное место на диске. Снова запускается Ексель и подключается эта надстройка. Теперь все, что она содержит, будет доступно Екселю в виде пользовательских ф-ций и процедур.

Добавлено:
aidomars, вай... Не знаю... Щаз посмотрю. Но я точно помню - не в первой колонке я был.


Добавлено:
aidomars
Совточно.
Field:The integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field one).

Транс-формИруюсь!
Sub sb_AutoFilter()
With ActiveCell
.AutoFilter Field:=.Column, Criteria1:=.Value
End With
End Sub

InSe0F, aidomars, asbo
Убедительная просьба для дальнейшего обсуждения перейти в топик по VBA для Excel http://forum.ru-board.com/topic.cgi?forum=33&topic=10903&glp
Заранее спасибо.

Mushroomer, сорри. Бес попутал - был уверен, что в VBA торчим :)

Mushroomer
Ну как бы кнопачку сделали для Excel, фильтр просто красата), просто вопрос плавно перетек из Excel в Excel VBA. Сорри если шо)

В формуле SUMMIF возможно ли сделать проверку двух условий?
Так работает:
СУММЕСЛИ(O5:O35;B40;U5:U35)
Если В40 совпадает с О... , то берется соответствующее U...

А хочется чтобы:
сравнивалось В40 с О... и А40 с К5:K35 и если оба условия совпадают, то тогда бы бралось соответствующее U...

Формула массива, навскидку
СУММЕСЛИ((O5:O35=B40)*(K5:K35=A40)*(U5:U35))

Цитата:

Формула массива, навскидку
СУММЕСЛИ((O5:O35=B40)*(K5:K35=A40)*(U5:U35))

Спасибо. Чтото не работает, аргументов должно быть три в формуле. Замена звездочек на ; разделители также ни чего не дает

SFC
Ясно, измени СУММЕСЛИ на СУММ и будет сумма по третьему столбцу при совпадении двух условий

Формула отображает #value, а при нажатии на fx результат видно, что правильный.

Добавлено:
Большое спасибо за подсказку - заработало. Сменил на SUMPRODUCT.