Здраствуйте! На форуме новичок и с excel пока на Вы. Прочитав материал предложенный на сайте - полного ответа к своей проблеме не нашел. Надеюсь на помощь сообщества ExcelWorld.
Вся проблема целиком: Есть массив с городами разбитыми по зонам, в зависимости от километража(1, 2, 3, 4 зоны доставки). И есть столбец с адресом покупателя(город, ул ., дом...).
Необходимо: создать формулу для подсчета количества доставок по каждой зоне. Как я вижу решение данной задачи: 1) Извлечь из ячейки с адресом ТОЛЬКО название города и зафиксировать его в соседней ячейке. 2) При наличии столбца с городами(пункт 1) и массива с зонами, написанными в 2 столбца(1й- название городов; 2й - номер зоны в которой находится) - применить ВПР.
Проблема с извлечениеи ТОЛЬКО названия города - вот там и появляются варианты, при которых "Текст по столбцам" не помогает.
Приветствуются любые варианты решения(с ВПР без ВПР, главное чтобы работало и экономило время) - пока этот процесс делается вручную...
П.С. В порядке бреда: Нельзя ли столбец с городами(из листа "Для ВПР") использовать как проверочный для каждой ячейки с адресом - при получении совпадения города из ячейки адреса с городом из столбца с городами - получать в отдельном столбце собственно наименование данного города - либо сразу зону в которой он находится... Возможно ли это?
Здраствуйте! На форуме новичок и с excel пока на Вы. Прочитав материал предложенный на сайте - полного ответа к своей проблеме не нашел. Надеюсь на помощь сообщества ExcelWorld.
Вся проблема целиком: Есть массив с городами разбитыми по зонам, в зависимости от километража(1, 2, 3, 4 зоны доставки). И есть столбец с адресом покупателя(город, ул ., дом...).
Необходимо: создать формулу для подсчета количества доставок по каждой зоне. Как я вижу решение данной задачи: 1) Извлечь из ячейки с адресом ТОЛЬКО название города и зафиксировать его в соседней ячейке. 2) При наличии столбца с городами(пункт 1) и массива с зонами, написанными в 2 столбца(1й- название городов; 2й - номер зоны в которой находится) - применить ВПР.
Проблема с извлечениеи ТОЛЬКО названия города - вот там и появляются варианты, при которых "Текст по столбцам" не помогает.
Приветствуются любые варианты решения(с ВПР без ВПР, главное чтобы работало и экономило время) - пока этот процесс делается вручную...
П.С. В порядке бреда: Нельзя ли столбец с городами(из листа "Для ВПР") использовать как проверочный для каждой ячейки с адресом - при получении совпадения города из ячейки адреса с городом из столбца с городами - получать в отдельном столбце собственно наименование данного города - либо сразу зону в которой он находится... Возможно ли это?Railord
Формулами навряд ли, макросами возможно, НО дайте в примере больше вариантов! а то не думаю что у вас везьде усть "ул." или просто г а не г. Также поясните если есть квадратные скобки то основной город игнорируеться?
Цитата
П.С. В порядке бреда:
Формулами навряд ли, макросами возможно, НО дайте в примере больше вариантов! а то не думаю что у вас везьде усть "ул." или просто г а не г. Также поясните если есть квадратные скобки то основной город игнорируеться?Poltava
Сообщение отредактировал Poltava - Вторник, 01.10.2013, 23:28
Формула массива. Вводить одновременным нажатием Контрл Шифт и Ентер. Почитайте в справке Excel. Только предупреждаю сразу - могут быть ошибки. Например, в случае СОЧИ [Дагомыс] формула берет то название города, которое в списке городов находится ниже
Так нужно? Как раз Ваше "В порядке бреда" идеальный вариант (на мой взгляд). Я так и подумал сделать еще до того, как дочитал Ваше сообщение до ПыСа.
Формула массива. Вводить одновременным нажатием Контрл Шифт и Ентер. Почитайте в справке Excel. Только предупреждаю сразу - могут быть ошибки. Например, в случае СОЧИ [Дагомыс] формула берет то название города, которое в списке городов находится ниже_Boroda_
Также поясните если есть квадратные скобки то основной город игнорируеться?
Да если есть примечание в квадратных скобках(и это не тире) - то основной город игнорируется...
Больше вариантов - не проблема, завтра буду на работе - выложу большой файлик ...
Boroda - вот это да!!! Кажется я снова начинаю верить в чудеса!) Да так и нужно!!! Осмыслить эту формулу я уже сегодня не смогу - и хорошо если завтра на свежую голову разберусь! Но теперь знаю точно - сам бы я ее ни за что не написал - спасибо огромное!)
П.С. По результатам тестирования обязательно отпишусь!
Также поясните если есть квадратные скобки то основной город игнорируеться?
Да если есть примечание в квадратных скобках(и это не тире) - то основной город игнорируется...
Больше вариантов - не проблема, завтра буду на работе - выложу большой файлик ...
Boroda - вот это да!!! Кажется я снова начинаю верить в чудеса!) Да так и нужно!!! Осмыслить эту формулу я уже сегодня не смогу - и хорошо если завтра на свежую голову разберусь! Но теперь знаю точно - сам бы я ее ни за что не написал - спасибо огромное!)
П.С. По результатам тестирования обязательно отпишусь!Railord
Nil novi sub luna, так что - век живи, век учись!)
Добрый день!) Как и обещал вчера, выкладываю больше вариантов - боевой(рабочий) вариант применения формулы! Видимо - корректно повторить её у меня не получилось, ибо работает она неправильно.
Среди прочих моих ошибок - я не указал ВСЕ условия применения формулы. 1) Иногда клетка адреса будет пустой, в этом случае номер зоны не должен возвращаться. 2) Когда в квадратных скобках тире - надо возвращать основной город. 3) Список с зонами будет дополняться и расширяться.
Boroda - проверьте пожалуйста правильность переписания формулы в приведенном мною файле. Видимо я ошибся. Было бы здорово учесть 3 эти условия.
Прилагаю рабочий файл.
Добрый день!) Как и обещал вчера, выкладываю больше вариантов - боевой(рабочий) вариант применения формулы! Видимо - корректно повторить её у меня не получилось, ибо работает она неправильно.
Среди прочих моих ошибок - я не указал ВСЕ условия применения формулы. 1) Иногда клетка адреса будет пустой, в этом случае номер зоны не должен возвращаться. 2) Когда в квадратных скобках тире - надо возвращать основной город. 3) Список с зонами будет дополняться и расширяться.
Boroda - проверьте пожалуйста правильность переписания формулы в приведенном мною файле. Видимо я ошибся. Было бы здорово учесть 3 эти условия.
Можно ведь несложную UDF написать - учесть там и [], и порядок поиска автоматически выстраивать, и ещё что-нибудь... Или обязательно только стандартными сложными формулами нужно делать?
Можно ведь несложную UDF написать - учесть там и [], и порядок поиска автоматически выстраивать, и ещё что-нибудь... Или обязательно только стандартными сложными формулами нужно делать?Hugo
1. Сделал через ЕСЛИ 2. А разве получается иначе? 3. Сделал динамические массивы (посмотреть можно, нажав Контрл F3) Ваша формула почти верна. Только тут: =ИНДЕКС(Зоны!B2:$B$60;МА... нужно написать =ИНДЕКС(Зоны!$B$2:$B$60;МА..., или =ИНДЕКС(Зоны!B$2:B$60;МА..., но перед двойкой доллар обязательно должен быть В общем, смотрите файл.
1. Сделал через ЕСЛИ 2. А разве получается иначе? 3. Сделал динамические массивы (посмотреть можно, нажав Контрл F3) Ваша формула почти верна. Только тут: =ИНДЕКС(Зоны!B2:$B$60;МА... нужно написать =ИНДЕКС(Зоны!$B$2:$B$60;МА..., или =ИНДЕКС(Зоны!B$2:B$60;МА..., но перед двойкой доллар обязательно должен быть В общем, смотрите файл._Boroda_
это из области VBA? Вообщем не для простых юзеров? Хотя из высказывания делаю вывод, что это легче чем формула массива?
Boroda - по последнему варианту формулы, все работает, протестил, но за исключением последнего штриха, в шапке маршрутника есть проблема с полем "адрес" - он его тоже возвращает 0(нулевой зоной)! Было бы очень круто поправить это, и сделать, чтобы эта ячейка напротив адреса оставалась пустой. Примеры на 45 и 64 строчках вложенного файла.
Оффтоп по пункту 3, если есть возможность скиньте пожалуйста ссылку(ткните пальцем) - где можно пройти ЛикБез по созданию динамического массива и формул массивов. Это для меня новый уровень в понимании excel.
это из области VBA? Вообщем не для простых юзеров? Хотя из высказывания делаю вывод, что это легче чем формула массива?
Boroda - по последнему варианту формулы, все работает, протестил, но за исключением последнего штриха, в шапке маршрутника есть проблема с полем "адрес" - он его тоже возвращает 0(нулевой зоной)! Было бы очень круто поправить это, и сделать, чтобы эта ячейка напротив адреса оставалась пустой. Примеры на 45 и 64 строчках вложенного файла.
Оффтоп по пункту 3, если есть возможность скиньте пожалуйста ссылку(ткните пальцем) - где можно пройти ЛикБез по созданию динамического массива и формул массивов. Это для меня новый уровень в понимании excel.Railord
По ссылке - я изучил данный материал сразу по приходу на сайт. Спасибо кстати создателям сайта - нашел для себя много нового!
Ну а по формуле - просто надо было мне это сразу предвидеть - и предупредить , а так да, она будет использоваться напротив шапки таблицы.
Теперь можно говорить о том, что формула работает на все 100 и даже больше - Boroda спасибо вам огромное за помощь! С динамическим массивом вроде тоже разобрался!
П.С. Пока писал данное сообщение выявилась еще одна погрешность , понимаю что уже задолбал, но сам потратив час ничего дельного не придумал . Строки 43 и 41 вложенного файла. В случае, когда название города не совпадает со списком - он выдает 0ую зону - а это приведёт к погрешности в подсчётах. Необходимо в таких случаях, чтобы формула возвращала ошибку или Н/Д или что-то другое, но не пустую ячейку и не 0.
По ссылке - я изучил данный материал сразу по приходу на сайт. Спасибо кстати создателям сайта - нашел для себя много нового!
Ну а по формуле - просто надо было мне это сразу предвидеть - и предупредить , а так да, она будет использоваться напротив шапки таблицы.
Теперь можно говорить о том, что формула работает на все 100 и даже больше - Boroda спасибо вам огромное за помощь! С динамическим массивом вроде тоже разобрался!
П.С. Пока писал данное сообщение выявилась еще одна погрешность , понимаю что уже задолбал, но сам потратив час ничего дельного не придумал . Строки 43 и 41 вложенного файла. В случае, когда название города не совпадает со списком - он выдает 0ую зону - а это приведёт к погрешности в подсчётах. Необходимо в таких случаях, чтобы формула возвращала ошибку или Н/Д или что-то другое, но не пустую ячейку и не 0.
Только предупреждаю сразу - могут быть ошибки. Например, в случае СОЧИ [Дагомыс] формула берет то название города, которое в списке городов находится ниже
По другим городам все пашет отлично, но в случае с Сочи - когда в скобках указывается город которого нет в спсике зон - он автоматом берет нулевую зону Сочи. Провозился с этим уже вот как 3 часа - но всё упирается в функцию МАКС, которую заменить нечем... Насколько я понял эту функцию, в случае не нахождения значения больше чем Сочи(0ая зона) - он возвращает её. Есть ли возможность изменить условие ЕСЛИ в МАКС под эту нужду? Или мне попробовать связаться с Hugo и поинтересоваться на счёт "несложной UDF"?
Файл прилагаю. 33,43 строки...
Доброго времени суток! Прошу открыть тему если её ещё можно открыть!
Сел считать Сочи, а тут то, о чем Вы и предупреждали в самом начале Boroda
Только предупреждаю сразу - могут быть ошибки. Например, в случае СОЧИ [Дагомыс] формула берет то название города, которое в списке городов находится ниже
По другим городам все пашет отлично, но в случае с Сочи - когда в скобках указывается город которого нет в спсике зон - он автоматом берет нулевую зону Сочи. Провозился с этим уже вот как 3 часа - но всё упирается в функцию МАКС, которую заменить нечем... Насколько я понял эту функцию, в случае не нахождения значения больше чем Сочи(0ая зона) - он возвращает её. Есть ли возможность изменить условие ЕСЛИ в МАКС под эту нужду? Или мне попробовать связаться с Hugo и поинтересоваться на счёт "несложной UDF"?
П.С. Рано радовался - не работает ... Попробую сам поправить... Поправлялка сломалась ...
С теми городами в скобках, которых нет в списке зон он теперь справляется удачно, но те города, которые есть в списке зон проходят теперь 0ой зоной и не важно в какой они зоне находится на самом деле...
59 и 60 строки файла...
Работает ! Благодарю!
П.С. Рано радовался - не работает ... Попробую сам поправить... Поправлялка сломалась ...
С теми городами в скобках, которых нет в списке зон он теперь справляется удачно, но те города, которые есть в списке зон проходят теперь 0ой зоной и не важно в какой они зоне находится на самом деле...
Доброго времени суток! Тема давно не поднималась - между тем, благодаря формуле написанной Boroda(спасибо тебе за это огромное) я смог произвести легкий!! подсчет доставок по всем регионам кроме Сочи, Сочи за октябрь я считал по старинке - вручную. Очень хочется избежать этого в ноябре
Но вопрос остался именно по Сочи. После последнего апгрейда формулы - она перестала считать города, которых нет в списке зон - и успешо присваивает им "ЗНАЧ", но вместе с тем проявилась другая болезнь - формула теперь всё считает нулевой зоной, даже то, что находится в 1-й 2-й.... и т.д. зонах. 59-61 строки вложенного файла.
П.С. - Я действительно старалcя сам исправить этот недостаток в формуле, но увы... Видимо необходимо проводить за excel несоизмеримо больше часов - чем мне удается отдавать формулам.
Доброго времени суток! Тема давно не поднималась - между тем, благодаря формуле написанной Boroda(спасибо тебе за это огромное) я смог произвести легкий!! подсчет доставок по всем регионам кроме Сочи, Сочи за октябрь я считал по старинке - вручную. Очень хочется избежать этого в ноябре
Но вопрос остался именно по Сочи. После последнего апгрейда формулы - она перестала считать города, которых нет в списке зон - и успешо присваивает им "ЗНАЧ", но вместе с тем проявилась другая болезнь - формула теперь всё считает нулевой зоной, даже то, что находится в 1-й 2-й.... и т.д. зонах. 59-61 строки вложенного файла.
П.С. - Я действительно старалcя сам исправить этот недостаток в формуле, но увы... Видимо необходимо проводить за excel несоизмеримо больше часов - чем мне удается отдавать формулам.Railord
Вход
Регистрация
Напомнить пароль
Автосумма
Часто используемые
ВПР
ЕСЛИ
СУММЕСЛИ
Финансовые
Логические
Текстовые
Даты и времени 
Ссылки и массивы 
Математические
Другие функции
Проверка свойств и значений
Баз данных
Математические
Обработки массивов
Обработки строк
Преобразования типов данных 
Загрузки данных
Работы с файлами
Обработки системных параметров
Обработки цвета
Работы с датами и временем
Преобразования в системы счисления
Финансовые
, понимаю что уже задолбал, но сам потратив час ничего дельного не придумал 
! Благодарю! 
