Страницы: 1
Упростить формулу
 
Для любителей разбираться с чужими формулами :)  
Вопрос в файле. Все работает, но не нравится формула, до чего-то не додумался. СУММПРОИЗВ не сработало .
 
симпотичные такие формулки :)  
какая именно не нравится?  
в столбце А - ниче так  
да и остальные тоже... в столбцах Д и Е длинноваты... но искусство требует жертв :)
 
Victtur!  
 
я не выдам решение вашей задачи, но дам пару советов, пишу в интерактиве поэтому возможны ошибки.  
 
1) прежде чем писать формулы, надо формализировать задачу, и прежде всего посотреть какие ограничения у Вас на исходные данные. Любое ограничение резко упрощает задачу.  
2)Вначале смотрим общую постановку:  
Извлечь из таблицы все строки для которых или в столбце 1смена или в столбце 2смена указана искомая фамилия.  
Ваши формулы данную общую задачу не решают!  
3) Иак смотрим на ограничения используемые в исходной таблице:  
-1: Если рабочий работал в 1смену то во вторую он не работает и соответственно наоборот  
-2: Для кажой недели рабочий обязательно работает и количество его смен за неделю =1 -одной неделе соответствует только одна смена данного рабочего  
-3 если посмотреть на формулу выдающую гомер недели:  
=ЕСЛИ(СЧЁТЕСЛИ($A$32:A34;МАКС($A$2:$A$30))=1;"";A34+1)то  
оказывается что подразумевается что недели (искомые строки) идут последовательно с номерами недель 1,2,3...  
до последней недели в исходной таблицы.  
 
4) В результате если учесть ограничения 1,2,3 получаем существенно более простую задачу чем общая  
 
для всех номеров недель 1,2,3, но не более наибольшего номера недели в исходных данных извлечь данные по условию  
если номер недели = указанному в столбце А и   в столбце 1смена или столбце 2смена стоит выбранная фамилия.  
То есть получается неоднакратно обсуждавшаяся задача выбрать уникальную запись по определяющим её условиям + сформировать список недель вида 1,2,3...  
до последней недели в исходной таблицы.
 
зы кстати получившаяся задача может быть решена и стандартными средствами эксель с помощью Бизвлечь и привлечением дополнительного столбца
 
так чуть покороче( и.., может быть :), попонятнее)
 
Мазаю. В ячейке Н31 примечание, видимо, при открытии листа оказалось спрятанным. Вопрос как раз по "длинноватым формулам в столбцах D и Е".  
 
Дед Лука, спасибо. Здорово, толково, подмечены моменты, которые в вопросе явно не прописаны.  
Формализация действительно хромает. Формула потому и не нравится, что сначала находится диапазон для определенной недели, потом в этом диапазоне определяется строка, далее находится положение этой строки в общем диапазоне.  
Но работает.  
{quote}{login=dl}{date=10.10.2008 11:25}{post}Ваши формулы данную общую задачу не решают!{/post}{/quote}  
Почему? Ведь все строки извлекаются. Есть "подводный камень"?  
{quote}{login=dl}{date=10.10.2008 11:25}{post}оказывается что подразумевается что недели (искомые строки) идут последовательно с номерами недель 1,2,3...{/post}{/quote}  
но недели могут начинаться не с первой (это № недели в году).  
 
слэну благодарность -  так, действительно, и короче, и понятнее; я в этом направлении недомудрил, похожую конструкцию: ($A$2:$A$30=A32)*(($D$2:$D$30=$H$1)+($E$2:$E$30=$H$1)) пробовал применить в СУММПРОИЗВ - не пошло.
 
А если копнут в сторону мегаформул? Не пробовали?
 
Прошу прощения, неправильно копирнул цитаты.  
   
dl: "Ваши формулы данную общую задачу не решают!"  
Почему? Ведь все строки извлекаются. Есть "подводный камень"?  
 
dl: "оказывается что подразумевается что недели (искомые строки) идут последовательно с номерами недель 1,2,3..."  
но недели могут начинаться не с первой (это № недели в году).
 
{quote}{login=Юрий М}{date=10.10.2008 11:56}{thema=}{post}А если копнут в сторону мегаформул? Не пробовали?{/post}{/quote}  
Зачем? Ведь формулы рабочие. Просто из спортивного интереса и повышения знаний хотелось упростить, ну не нравились они мне :)
 
Ясно. Я сначала понял так, что задача "укоротить",- потому и предложил.
 
Зацените, пожалуйста, мой вариантик во вложении. Не то, чтобы упростил. Просто несколько иной, скажем, ТУРБО-ЁЖе-подход.
 
Понравилась запись диапазонов, разбираться буду завтра.  
А кто стащил список для Н1? :)
 
{quote}{login=vikttur}{date=13.10.08 02:40}{thema=}{post}  
А кто стащил список для Н1? :){/post}{/quote}  
 
Извини, увлёкся и случайно откусил.    
 
** ТУРБО-ЁЖ **, судя по фото, мы почти ровесники :)  
Не понял, как работает функция СУММ(СТРОКА($I$1:$I$29)*(ТИП.ОШИБКИ...) - № строки и (2 или з)?  
Такая хитрая запись диапазона - A:A $2:$30- для облегчения ввода или любовь к необычностям?  
В НАЙТИ начальную_позицию можно не писать.
 
обычная запись - если через пробел, значит пересечение диапазонов - в данном случае столбец может меняться(ссылка относительная), а строки жестко со 2й по 30ю  
 
просто редко кто пользуется, видимо  пробел интуитивно непонятен :)
 
{quote}{login=vikttur}{date=10.10.2008 11:57}{thema=}{post}Прошу прощения, неправильно копирнул цитаты.  
   
dl: "Ваши формулы данную общую задачу не решают!"  
Почему? Ведь все строки извлекаются. Есть "подводный камень"?  
 
dl: "оказывается что подразумевается что недели (искомые строки) идут последовательно с номерами недель 1,2,3..."  
но недели могут начинаться не с первой (это № недели в году).{/post}{/quote}  
 
если нарушить любое из перечисленных мною условий всё идёт насмарку, в том числе и формулы "эффективного турбоежа".  
(Ёж это не наезд, ибо я как разумный человек знаю, что наезды на ежей чреваты)  
кстати ещё заметил что на самом деле поиск по рабочему делается последовательно для каждых трёх строк, это всегда так?
 
{quote}{login=DL}{date=14.10.2008 11:25}{thema=Re: }{post}Если нарушить любое из перечисленных мною условий, всё идёт насмарку, в том числе и формулы "эффективного турбоежа".{/post}{/quote}  
 
Извините, какие условия вы имеете ввиду? Под условиями Вы подразумеваете предложение расшифровать ограничения для упрощения задачи?  
 
Если да, то моей формуле всё нипочём:  
- сотрудник может пахать обе смены;  
- смены сами могут работать без сотрудника :)  
- недели могут начинаться с любой, идти не подряд и в любой последовательности или попросту отсутствовать.  
- в неделе может быть не три, а сколько угодно пар.  
Что-то упустил?  
 
Я её для этого не предусматривал, это получилось само собой ввиду такого подхода (о нём скажу в след. топике).  
 
Единственное, если один из компаньонов заболел, и сотруднику пришлось работать в нескольких парах на одной неделе, то формула отдаст его заслуги другим :)
 
{quote}{login=vikttur}{date=14.10.08 09:50}{thema=}{post}** ТУРБО-ЁЖ **, судя по фото, мы почти ровесники :)  
Не понял, как работает функция  
Такая хитрая запись диапазона - A:A $2:$30 - для облегчения ввода или любовь к необычностям?  
В НАЙТИ начальную_позицию можно не писать.{/post}{/quote}  
 
Если точный возраст не указан, то, по умолчанию, мы – сверстники :)  
Хитрая запись диапазона – любовь к необычностям и любовь к красоте записи. Поскольку стандартное обозначение конкатенации четырёх диапазонов превращает формулу в мешанину символов, решил использовать запись через пересечение, чтобы порадовать глаз :)  
 
Функция НАЙТИ возвращает #ЗНАЧ!, если искомое не найдено. Это я проверяю функцией ТИП.ОШИБКИ. Но она возвращает #Н/Д, если ей передаётся неошибочное значение. Поэтому я НАЙТИ делю на нуль, чтобы получить ошибку в любом случае.  
 
СТРОКА($I$1:$I$29) даёт массив символов от 1 до 29.  
ТИП.ОШИБКИ()=2 даёт массив из ЛОЖЬ, ИСТИНА  
Их произведение даст {0;0;0;4;0;0;…}. СУММ сложит нулики и четвёрку.  
 
Также следует заметить, что формула массива введена сразу в две ячейки. Можно, конечно, было сделать для каждой по отдельности, но ведь нам надо сразу два значения, поэтому я просто для СМЕЩ задал [ширину] в 2 ячейки – считайте это тоже хитрой записью по тем же причинам ;D
 
Вы правы, нач.поз. для НАЙТИ – здесь лишняя. Спасибо.
 
{quote}{login=** ТУРБО-ЁЖ **}{date=14.10.2008 05:29}{thema=Re[16]: Упростить формулу}{post}{quote}{login=DL}{date=14.10.2008 11:25}{thema=Re: }{post}Если нарушить любое из перечисленных мною условий, всё идёт насмарку, в том числе и формулы "эффективного турбоежа".{/post}{/quote}
 
Извините, какие условия вы имеете ввиду? Под условиями Вы подразумеваете предложение расшифровать ограничения для упрощения задачи?  
 
{/post}{/quote}  
 
Я имел ввиду общую задачу: отобрать из таблицы все строки где или в столбце D или E фамилия из H1.  
 
По поводу пользы расшифровки  
Смотрим на Вашу формулу  
НАЙТИ($A32&$H$1;A:A $2:$30&D:D $2:$30&A:A $2:$30&E:E $2:$30;1)  
она выделяет все строки для которых номер недели совпадает с искомым, и в столбцах D,E присутствует выбранная фамилия. В силу приведённых мною ограничений, такая строка существует и является единственной! соответственно последующая проверка на ошибку не нужна!  
кстати Ваша формула и не прописывает что делать если найденных строк несколько или наоборот совсем нет.  
 
Отказавшись от проверки ошибки и слегка переписав условие отбора строк можно слегка уменьшить длину формулы  
=ЕСЛИ($A32="";"";СМЕЩ(D$1;СУММ(СТРОКА(I:I $1:$29)*ЕЧИСЛО(НАЙТИ($H$1;D:D $2:$30&E:E $2:$30;1))*(A:A $2:$30=A32));0;;2))  
Естественно что ей свойственны те же ошибки что и формулам приведённым в предыдущих вариантах решения задачи  
 
поэтому приведу общую формулу отбора всех строк по условию задачи формула массива возвращающая массив  
=ИНДЕКС(A$2:G$31;НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(НАЙТИ($H$1;D:D $2:$30&E:E $2:$30));ПОИСКПОЗ(A2:A30&D2:D30&E2:E30;A2:A30&D2:D30&E2:E30;0);30);СТРОКА(D$1:D$30));СТОЛБЕЦ())  
столбцы F,G включены в результирующий массив  всилу того что выбранные строки в исходном массиве не повторяются, если это не так итого нужно считать отдельным формулами суммирования по условию.  
 
пример в файле
 
Мужики, я доволен. Цитирую себя:)  
"Просто из спортивного интереса и повышения знаний хотелось упростить, ну не нравились они мне :)"  
А получилась живое интересное обсуждение.  
Спасибо.
 
{quote}{login=vikttur}{date=16.10.08 00:56}{thema=}{post}Получилась живое интересное обсуждение.{/post}{/quote}  
 
Вы ведь не будете против, если я ещё разок поэксплуатирую ваш пример в качестве полигона для оттачивания мастерства?  
 
DL, Вы составили формулу для решения всей задачи, а не только для смущавших vikttur'а столбцов. Короче, я решил последовать вашему примеру, постаравшись упростить всё в целом (см. вложение).
 
Прочитал тему с удовольствием :)  
Решение красиво...
 
Самое печальное. что мне так и не удалось получить красивых формул для решения задачи с учётом приведённых мной ограничений.  
Не говоря уж об общем решение, даже вариант 3 Соника (Турбо-Ёж), выглядит гораздо компактней.  
 
Правда это пока "красивость" эквивалентна компактности, а вот если кроме красивости учесть еще и скорость пересчёта данных при их изменении, может быть другой коленкор. Для исходной таблицы в пять - десяь тысяч строк общее решение учитывающее всю таблицу начнёт тормозить.  
 
Вобще идеальным было бы решение с дополнительным условием и автофильтром по этому условию.  
НО автофильтр работает по принципу "мавр сделал своё дело - скрыл столбцы, мавр может уйти". То есть если в область автофильтра внечти формулы, то при их пересчёте автофильтр не реагирует на изменение данных в ячейках.  
В результате надо использовать VBA чтоб заставить его снова поработать над скрытием столбцов по новым данным.
Страницы: 1
Читают тему
Наверх