[oodisc] задача: обновление полей сводного документа на основе имеющихся данных в Calc

[Орлов Владислав]

В сообщении от Вторник, 18-Окт-2005 16:40 Круглов Андрей Константинович 
написал(a):
> На листе 1 имется 1000 строк с фамилиями и напротив каждой фамилии есть
> некоторые данные.
> На листе 2 имеется 400 строк с фамилиями и напротив каждой фамилии есть
> другие данные.
> Требуется: определить строки (фамилии) второго листа, которых нет в
> первом списке.
Первый лист уже отсортирован по алфавиту? А второй? Это намного облегчило бы 
работу Калку (и пользователю :-)) Поэтому для начала сортируем данные на 
обоих листах по ключевому полю - в нашем случае по фамилии.
Фишка в том, что vlookup (ВПР в терминах MSO) с последним параметром "не 
ноль" (ИСТИНА, в смысле) или находит значение в диапазоне или выдает "нет 
данных" (Н/Д). А вот в случае "ЛОЖЬ" он нам найдет "ближайший похожий".
Используем эту особенность для поиска людей, отсутствующих в первом листе.
В последнем столбце (в первом свободном) второго листа в заголовке пишем 
что-то вроде ТЕСТ (заголовок нашего вспомогательного столбца), а в первой же 
клетке после заголовка таблицы пишем формулу 
=IF(ISERROR(VLOOKUP('ФИО';$DANO.$A$2:$F$1000;1;1));"Новый";"Уже есть")
Эту формулу конструировал исходя из следующих соображений:
1. Столбец с фамилиями называется именно так - ФИО (кстати, использование 
имени столбца в качестве параметра гарантированно сработает только если 
столбец вплотную примыкает к массиву данных - если разграничены пустым 
столбцом, то придется использовать прямую ссылку типа $A2.)
2. Первый лист называется DANO и данные в нем в столбцах A-F и в строках 
2-1000. (В принципе, для решения первой задачи можно было бы ограничится 
только одним столбцом - тем, в котором сидят фамилии. Но лучше указать 
побольше данных - в случае если в формулу будут включаться дополнительные 
данные - например, однофамильцы из разных отделов - не придется переписывать 
адрес массива данных). Ссылку на массив даем абсолютную, с 
"долларами" (Shift+F4) - когда будем формулу "размножать" она не превратится 
в DANO.A3:F1001, DANO.A4:F1002, DANO.A4:F1003 и т.д.
3. Для дальнейшей обработки достаточно текстовых значений "Новый" или "Уже 
есть" (это могли бы быть и числа или просто логические значения)
Что делает эта формула? Просто убеждается, что значение в колонке ФИО в 
текущей строке есть на листе DANO.
Самое сложное теперь - растянуть эту формулу вниз до последней из заполненных 
строк (400-ой в нашем примере). В MSO для этого используется двойной щелчок 
по маркеру выделения (или как он там называется - квадратик в нижнем правом 
углу клетки?)
Лично я после того как настроил горячую клавишу Ctrl+D для 
Правка-Заполнить-Вниз заполняю столбец так: стрелка влево (на столбец с 
данными), Ctrl+вниз (до последней заполненой строки - если промахнулся, то 
один раз Ctrl+вверх), стрелка вправо (на столбец, где где-то в самом верху 
стоит наша формула), Ctrl+Shift+вверх (выделяются все клетки столбца, начиная 
с клетки с формулой) и Ctrl+D... Телемаркет! В выделенном столбце чередуются 
"Новый" и "Уже есть".
Структура данных на обоих листах одинакова? Шикарно! Тогда дальше очень 
просто: Данные - Фильтр - Стандартный фильтр 
Условие1 ТЕСТ = Уже есть
Детали >>
Область содержит заголовки столбцов - флажок снимаем (зачем нам второй 
заголовок?)
Копировать результат в - флажок ставим. После этого в поле адреса можем или от 
руки вписать $DANO.$A$1001 или ткнуть мышью в первое свободное поле внизу 
таблицы на листе DANO.
Готово. Данные о новых ребятах дописаны к старым. Но! Дописаны вместе с 
формулами из столбца ТЕСТ. Ctrl+вправо, Ctrl+вниз, Ctrl+Shift+вверх, Delete. 
Формул больше нет.
Данные неотсортированы. Влево (на клетку с данными), Ctrl+*. Данные выделены. 
Из меню Данные - Сортировка... ФИО...
> Причем это сделать надо быстро, на автомате: щелк - выделились строки,
> которых нет в первом листе, второй щелк - добавили эти строки в первый
> лист. Еще раз щелк - выделяем оставшиеся строки (фамилия совпадает), щелк -
> третий, пятый и десятый столбец первого листа заменился на значения из
> второго листа, а остальные столбцы остались без изменений.
Упс... За один "щелк" вряд ли получится... Но! Нам ведь не нужно ВСЕХ 
оставшихся обновлять - только ИЗМЕНИВШИХСЯ с прошлого раза... А-а-а! Если они 
попали в эти 400 строк, значит изменились? Тоже не бином Ньютона...
Здесь, правда, одним дополнительным столбцом не обойтись - понадобится новый 
лист. Но и это ведь не сложно!
Итак, опять пошагово:
Правый клик на ярлычке DANO - Переместить/копировать, флажок Копировать, 
Переместить в конец... Получили лист DANO_2 над которым будем изга... изго... 
извра... работать, в общем. Для начала вычищаем все кроме столбца фамилий. 
B2, Ctrl+End, Delete. И возвращаемся в C2.
=VLOOKUP($A2;$NEW.$A$2:$F$400;3;1)
(В смысле, лист с обновлениями у нас называется NEW и для столбца C нам нужны 
данные из третьего столбца массива. Почему третьего? По условию задачи). 
Скорее всего сразу же получим Н/Д. Это не страшно, главное чтобы не ССЫЛ - 
если ССЫЛ или какая-то другая ошибка, значит в формулу вкралась ошибка.
Копируем КЛЕТКУ вместе сформулой в пятый и десятый столбцы, подправляем в 
формуле предпоследний параметр на 5 и 10 соответственно. Это хорошо, что в 
первом параметре мы написали $A2, а не просто A2 - иначе пришлось бы 
восстанавливать его в каждой из формул.
Размножаем эти три формулы: Ctrl+Home, Ctrl+вниз, вправо (один раз!), 
Ctrl+Shit+End (последняя строка выделена), Ctrl+Shift+вверх (прыгнули с 
выделением вверх или до второй строки или до заголовка - зависит от того была 
ли формула в последнем столбце. Даже если перепрыгнули - не страшно: 
Shift+вниз). А теперь - Ctrl+D. Третий, пятый и десятый заполнены данными или 
Н/Д. Выделение все еще есть? Отлично! Ctrl+C (или Ctrl+Insert) - все что надо 
в буфере обмена.
Правка-Вставить как (я на эту команду повесил Ctrl+Shift+Insert, чтобы в меню 
не лазить). Здесь ВНИМАТЕЛЬНО! Не вставить ВСЕ, а только Строки, Числа, 
Даты/время - главное НЕ вставить формулы: мы сейчас как раз от формул и 
избавляемся. Теперь, когда Н/Д из результата вычислений превратился просто в 
строку "#Н/Д", ее легко и непринужденно "множим на ноль" (Ctrl+F, Найти #Н/Д, 
Заменить на отавляем пустым).
Что в результате? Только клетки с данными из листа NEW и эти данные стоят 
именно в тех местах, где и должны - только не в листе DANO, а в DANO_2. Ну 
это уж совсем просто: Ctrl+Home, вправо, вниз (стоим в B2), Ctrl+Shift+End 
(данные и пустые клетки выделены), Ctrl+Ins (в буфер обмена), Ctrl+PgUp 
нужное количество раз (чтобы открыть лист DANO), опять Ctrl+Home, вправо, 
вниз (чтобы и здесь стать в B2), Ctrl+Shift+Insert (или "Правка-Вставить 
как", если до сих пор клавиша не назначена) и - опять внимание! В секции 
Параметры есть великолепный флажок - Пропуск пустых ячеек. То есть, если в 
DANO_2 у нас где-то была пустая ячейка (а мы их сами сделали, когда заменяли 
Н/Д на пустую строку), то данные в исходном массиве не изменятся! Оп-ля! 
Удаляем лист DANO_2. Задача решена.
> Если у меня, очень продвинутого пользователя, эта задача на час работы,
> то у простого пользователя она занимает день минимум.
М-да... Без комментариев...
> Нужен способ (технология) для того, чтобы упростить эту работу.
Вот не помню, то ли у Кнута, то ли у Ван Тассела фраза есть: "Даже если до 
сдачи проекта осталось всего полчаса, половину этого времени посвятите 
обдумыванию задачи. Не исключено, что вы найдете гениальное в своей простоте 
решение..." (переврал по памяти, но близко к тексту)
> Вы посмотрите - всё, что я описал в щелчках - делается на раз. Даже
> самый непродвинутый пользователь умеет делать эту работу. Основной
> напрасный расход человеческой энергии - когда человек ВРУЧНУЮ
> определяет, что столбы одинаковы. 
И если мы дадим ему рыбу (читай, сделаем за него эту хрень) - он будет сыт 
целый день, а если научим ловить рыбу - будет сыт всю жизнь...
> Эта операция составляет 90% времени от 
> общего времени, потраченного на такого рода работу.
> Это должен делать компьютер: 
Точно-точно! Девиз IBM - Think! (в смысле: Компьютер должен работать, а 
человек - думать)
> Понимаете логику?
Не-а :-)
> Можете предложить способ для определения "одинаковости" строк? Может мой
> взгляд "замылен" MSO и я его просто не знаю??
Знаете, Андрей Константинович, все что я здесь понаписывал я отрабатывал 
именно на MSO несколько лет назад. На ООо просто перенес навыки... Они - 
офисы эти - абсолютно одинаковы! В том смысле, что и тот и другой не боле чем 
инструмент. (Только ООо - лучше... Уже год как не устаю в этом убеждаться)

-- 
Всего доброго!
Владислав Орлов
Начальник отдела программного обеспечения
ЗАО СК "Профессиональная Защита" ("Про-За")
ICQ UIN 18873415

PS. Для большего удобства общения настоятельно рекомендую установить себе 
OpenOffice.org - он того стоит (хотя и совершенно бесплатен!)