Домашняя страница Undo Do New Save Карта сайта Обратная связь Поиск по форуму
МИР MS EXCEL - Гость.xls

Вход

Регистрация

Напомнить пароль

 

= Мир MS Excel/Поиск последнего вхождения фразы в документе - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск последнего вхождения фразы в документе (Формулы/Formulas)
Поиск последнего вхождения фразы в документе
and_evg Дата: Вторник, 25.07.2017, 12:22 | Сообщение № 1
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 157
Репутация: 30 ±
Замечаний: 0% ±

Excel 2007
Всем добрый день!
Постоянно получаю документы (акт КС-2) сформированные в сторонней программе (см. лист «Акт 16 граф» в прилагаемом файле).
Данный акт может состоять из одного или нескольких разделов (в данном случае Раздел 1, Раздел 2, Раздел 3).
По каждому разделу могут подбиваться итоги, а могут и не подбиваться (помечены зеленым). В частности такие строки как:
Материалы
Машины и механизмы
ФОТ
Накладные расходы
Сметная прибыль
Так же подбивается итог в целом по акту. Обязательно присутствует хотя бы одна из строк (помечены желтым):
Материалы
Машины и механизмы
ФОТ
Накладные расходы
Сметная прибыль
Как найти данные строки (только из итогов в целом по акту) и проставить соответствующие суммы как в «Лист1». Заранее спасибо.
К сообщению приложен файл: 60298-4.-6.xls(54Kb)
 
Ответить
СообщениеВсем добрый день!
Постоянно получаю документы (акт КС-2) сформированные в сторонней программе (см. лист «Акт 16 граф» в прилагаемом файле).
Данный акт может состоять из одного или нескольких разделов (в данном случае Раздел 1, Раздел 2, Раздел 3).
По каждому разделу могут подбиваться итоги, а могут и не подбиваться (помечены зеленым). В частности такие строки как:
Материалы
Машины и механизмы
ФОТ
Накладные расходы
Сметная прибыль
Так же подбивается итог в целом по акту. Обязательно присутствует хотя бы одна из строк (помечены желтым):
Материалы
Машины и механизмы
ФОТ
Накладные расходы
Сметная прибыль
Как найти данные строки (только из итогов в целом по акту) и проставить соответствующие суммы как в «Лист1». Заранее спасибо.

Автор - and_evg
Дата добавления - 25.07.2017 в 12:22
китин Дата: Вторник, 25.07.2017, 12:27 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4273
Репутация: 665 ±
Замечаний: 0% ±

Excel 2007;Excel 2010
как понял
Код
=ПРОСМОТР(;-1/('Акт 16 граф'!$A$37:$A$97=Лист1!A1);'Акт 16 граф'!$K$37:$K$96)
К сообщению приложен файл: and_evg.xlsx(23Kb)


Не судите строго:я пытаюсь научиться
ЯД 41001877306852/WM R249698041931; Z239672726538


Сообщение отредактировал китин - Вторник, 25.07.2017, 12:27
 
Ответить
Сообщениекак понял
Код
=ПРОСМОТР(;-1/('Акт 16 граф'!$A$37:$A$97=Лист1!A1);'Акт 16 граф'!$K$37:$K$96)

Автор - китин
Дата добавления - 25.07.2017 в 12:27
sboy Дата: Вторник, 25.07.2017, 12:32 | Сообщение № 3
Группа: Проверенные
Ранг: Ветеран
Сообщений: 841
Репутация: 224 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
Еще вариант (формула массива)
Код
=МАКС(ИНДЕКС(ЕСЛИ(ЕЧИСЛО('Акт 16 граф'!$K$30:$K$100);'Акт 16 граф'!$K$30:$K$100;)*('Акт 16 граф'!$A$30:$A$100=A1);))
К сообщению приложен файл: 60298-4-6.xls(52Kb)
 
Ответить
СообщениеДобрый день.
Еще вариант (формула массива)
Код
=МАКС(ИНДЕКС(ЕСЛИ(ЕЧИСЛО('Акт 16 граф'!$K$30:$K$100);'Акт 16 граф'!$K$30:$K$100;)*('Акт 16 граф'!$A$30:$A$100=A1);))

Автор - sboy
Дата добавления - 25.07.2017 в 12:32
_Boroda_ Дата: Вторник, 25.07.2017, 13:15 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 10955
Репутация: 4553 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Еще вариант на ИНДЕКСах - он, хоть и длинный, но шустрый должен быть
Код
=ИНДЕКС('Акт 16 граф'!K:K;ПОИСКПОЗ(A1;ИНДЕКС('Акт 16 граф'!A:A;ПОИСКПОЗ("ВСЕГО по акту";'Акт 16 граф'!A:A;)):'Акт 16 граф'!A$999;)+ПОИСКПОЗ("ВСЕГО по акту";'Акт 16 граф'!A$1:A$999;)-1)

и с учетом того, что промежуточные итоги все-таки подбиваются
Код
=СУММЕСЛИ('Акт 16 граф'!A:A;A1;'Акт 16 граф'!K:K)/2
К сообщению приложен файл: 60298-4-6_1.xls(56Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеЕще вариант на ИНДЕКСах - он, хоть и длинный, но шустрый должен быть
Код
=ИНДЕКС('Акт 16 граф'!K:K;ПОИСКПОЗ(A1;ИНДЕКС('Акт 16 граф'!A:A;ПОИСКПОЗ("ВСЕГО по акту";'Акт 16 граф'!A:A;)):'Акт 16 граф'!A$999;)+ПОИСКПОЗ("ВСЕГО по акту";'Акт 16 граф'!A$1:A$999;)-1)

и с учетом того, что промежуточные итоги все-таки подбиваются
Код
=СУММЕСЛИ('Акт 16 граф'!A:A;A1;'Акт 16 граф'!K:K)/2

Автор - _Boroda_
Дата добавления - 25.07.2017 в 13:15
and_evg Дата: Вторник, 25.07.2017, 14:25 | Сообщение № 5
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 157
Репутация: 30 ±
Замечаний: 0% ±

Excel 2007
Всем СПАСИБО! Почти все работает!
Интересно было бы разобраться с формулой
Код
=ПРОСМОТР(;-1/('Акт 16 граф'!$A$37:$A$97=Лист1!A1);'Акт 16 граф'!$K$37:$K$96)

непонятно несколько моментов:
1. Почему пусто "искомое_значение"?
2. что значит выражение в просматриваемом векторе
Код
-1/('Акт 16 граф'!$A$37:$A$97=Лист1!A1)

3. из справки Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может вернуть неверный результат но в получаемых актах никакой сортировки нет. Не повлияет это на результат?
 
Ответить
СообщениеВсем СПАСИБО! Почти все работает!
Интересно было бы разобраться с формулой
Код
=ПРОСМОТР(;-1/('Акт 16 граф'!$A$37:$A$97=Лист1!A1);'Акт 16 граф'!$K$37:$K$96)

непонятно несколько моментов:
1. Почему пусто "искомое_значение"?
2. что значит выражение в просматриваемом векторе
Код
-1/('Акт 16 граф'!$A$37:$A$97=Лист1!A1)

3. из справки Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания: ..., -2, -1, 0, 1, 2, ..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может вернуть неверный результат но в получаемых актах никакой сортировки нет. Не повлияет это на результат?

Автор - and_evg
Дата добавления - 25.07.2017 в 14:25
_Boroda_ Дата: Вторник, 25.07.2017, 14:29 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 10955
Репутация: 4553 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Посмотрите здесь я для другого ПРОСМОТР писал, посложнее немного, но так даже лучше
http://www.excelworld.ru/forum/2-16573-138042-16-1427281717


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПосмотрите здесь я для другого ПРОСМОТР писал, посложнее немного, но так даже лучше
http://www.excelworld.ru/forum/2-16573-138042-16-1427281717

Автор - _Boroda_
Дата добавления - 25.07.2017 в 14:29
and_evg Дата: Вторник, 25.07.2017, 16:17 | Сообщение № 7
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 157
Репутация: 30 ±
Замечаний: 0% ±

Excel 2007
_Boroda_, Пожалуйста поправьте меня если я не правильно понял (хочется для себя уяснить).
1."искомое_значение" - пустое, аналогично нулю. Следовательно ищем заведомо несуществующий элемент.
2. Конструкция -1/('Акт 16 граф'!$A$37:$A$97=Лист1!A1), в случае если что то нашлось, возвращает массив состоящий из -1, в противном случае ошибку.
3. Так как функция ПРОСМОТР предполагает, что поиск происходит в отсортированном массиве по возрастанию и в случае неудачи возвращает наибольшее значение из найденных, то возвращается последняя строка.
 
Ответить
Сообщение_Boroda_, Пожалуйста поправьте меня если я не правильно понял (хочется для себя уяснить).
1."искомое_значение" - пустое, аналогично нулю. Следовательно ищем заведомо несуществующий элемент.
2. Конструкция -1/('Акт 16 граф'!$A$37:$A$97=Лист1!A1), в случае если что то нашлось, возвращает массив состоящий из -1, в противном случае ошибку.
3. Так как функция ПРОСМОТР предполагает, что поиск происходит в отсортированном массиве по возрастанию и в случае неудачи возвращает наибольшее значение из найденных, то возвращается последняя строка.

Автор - and_evg
Дата добавления - 25.07.2017 в 16:17
_Boroda_ Дата: Вторник, 25.07.2017, 16:25 | Сообщение № 8
Группа: Модераторы
Ранг: Местный житель
Сообщений: 10955
Репутация: 4553 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
1. "искомое_значение" - пустое, аналогично нулю. Следовательно ищем заведомо несуществующий элемент." Несуществующий и, что очень важно, больший любого из возможных значений, элемент
2. "Конструкция ... в случае если что то нашлось, возвращает массив состоящий из -1, в противном случае ошибку" констукция в любом случае возвращает массив. А вот состоит он из -1 в случае, когда что-то нашлось, и ошибки, когда не нашлось
3. "в случае неудачи возвращает наибольшее значение из найденных" В случае неудачи (вообще ничего не найдено - это неудача) возвращается значение ошибки. А вот в случае, когда одно или несколько значений все-таки найдено, вот тогда да - "возвращается последняя строка". Но опять же, из тех, которые были найдены


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение1. "искомое_значение" - пустое, аналогично нулю. Следовательно ищем заведомо несуществующий элемент." Несуществующий и, что очень важно, больший любого из возможных значений, элемент
2. "Конструкция ... в случае если что то нашлось, возвращает массив состоящий из -1, в противном случае ошибку" констукция в любом случае возвращает массив. А вот состоит он из -1 в случае, когда что-то нашлось, и ошибки, когда не нашлось
3. "в случае неудачи возвращает наибольшее значение из найденных" В случае неудачи (вообще ничего не найдено - это неудача) возвращается значение ошибки. А вот в случае, когда одно или несколько значений все-таки найдено, вот тогда да - "возвращается последняя строка". Но опять же, из тех, которые были найдены

Автор - _Boroda_
Дата добавления - 25.07.2017 в 16:25
and_evg Дата: Среда, 26.07.2017, 09:51 | Сообщение № 9
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 157
Репутация: 30 ±
Замечаний: 0% ±

Excel 2007
Спасибо за развернутый ответ.
Но, если ИСТИНА=1 ЛОЖЬ=0 догадываюсь, что операция деления применяется для генерации ошибки на тот случай если ничего не нашлось.
А если допустить, что в просматриваемом диапазоне 100% есть искомая фраза (как в моем файле), то получается что -1/1=1 равносильна операции -1*1=1
Однако конструкция -1*('Акт 16 граф'!$A$37:$A$97=Лист1!A1) дает неверный результат. Почему?
 
Ответить
СообщениеСпасибо за развернутый ответ.
Но, если ИСТИНА=1 ЛОЖЬ=0 догадываюсь, что операция деления применяется для генерации ошибки на тот случай если ничего не нашлось.
А если допустить, что в просматриваемом диапазоне 100% есть искомая фраза (как в моем файле), то получается что -1/1=1 равносильна операции -1*1=1
Однако конструкция -1*('Акт 16 граф'!$A$37:$A$97=Лист1!A1) дает неверный результат. Почему?

Автор - and_evg
Дата добавления - 26.07.2017 в 09:51
sboy Дата: Среда, 26.07.2017, 09:56 | Сообщение № 10
Группа: Проверенные
Ранг: Ветеран
Сообщений: 841
Репутация: 224 ±
Замечаний: 0% ±

Excel 2010
and_evg, -1*0 будет 0, а не ошибка.
 
Ответить
Сообщениеand_evg, -1*0 будет 0, а не ошибка.

Автор - sboy
Дата добавления - 26.07.2017 в 09:56
_Boroda_ Дата: Среда, 26.07.2017, 10:02 | Сообщение № 11
Группа: Модераторы
Ранг: Местный житель
Сообщений: 10955
Репутация: 4553 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
то получается что -1/1=1 равносильна операции -1*1=1

В Вашем варианте написания да, так и есть. Но не забывайте, что в знаменателе у нас не одно значение, а МАССИВ
-1/('Акт 16 граф'!$A$37:$A$97=Лист1!A1) даст примерно вот так
{#ДЕЛ/0!:-1:#ДЕЛ/0!:-1:#ДЕЛ/0!:-1:#ДЕЛ/0!:-1:#ДЕЛ/0!:-1:...}


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
то получается что -1/1=1 равносильна операции -1*1=1

В Вашем варианте написания да, так и есть. Но не забывайте, что в знаменателе у нас не одно значение, а МАССИВ
-1/('Акт 16 граф'!$A$37:$A$97=Лист1!A1) даст примерно вот так
{#ДЕЛ/0!:-1:#ДЕЛ/0!:-1:#ДЕЛ/0!:-1:#ДЕЛ/0!:-1:#ДЕЛ/0!:-1:...}

Автор - _Boroda_
Дата добавления - 26.07.2017 в 10:02
and_evg Дата: Среда, 26.07.2017, 10:11 | Сообщение № 12
Группа: Проверенные
Ранг: Форумчанин
Сообщений: 157
Репутация: 30 ±
Замечаний: 0% ±

Excel 2007
у нас не одно значение, а МАССИВ

Тогда понятно. для -1/1(0)=-1(#ДЕЛ/0) получаем {#ДЕЛ/0!:-1:#ДЕЛ/0!:-1:#ДЕЛ/0!:-1:#ДЕЛ/0!:-1:#ДЕЛ/0!:-1:...}, а для -1*1(0)=-1(0) получаем {0:-1:0:-1:0:-1:0:-1:0:-1:...} В моем случае и выдает искомый ноль! :D
СПАСИБО!
 
Ответить
Сообщение
у нас не одно значение, а МАССИВ

Тогда понятно. для -1/1(0)=-1(#ДЕЛ/0) получаем {#ДЕЛ/0!:-1:#ДЕЛ/0!:-1:#ДЕЛ/0!:-1:#ДЕЛ/0!:-1:#ДЕЛ/0!:-1:...}, а для -1*1(0)=-1(0) получаем {0:-1:0:-1:0:-1:0:-1:0:-1:...} В моем случае и выдает искомый ноль! :D
СПАСИБО!

Автор - and_evg
Дата добавления - 26.07.2017 в 10:11
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск последнего вхождения фразы в документе (Формулы/Formulas)
Страница 1 из 11
Поиск:

Яндекс цитирования
© 2010-2017 · Дизайн: MichaelCH · Хостинг от uCoz · При использовании материалов сайта, ссылка на www.excelworld.ru обязательна!