MS Excel - решение практических задач

В этой теме размещаем вопросы и ответы, а также советы и рекомендации по решению конкретных задач, которые требуют создания формулы, применения макроса, написания кода VBA, т.е. Практикум.

------------------------------

Вопросы и ответы, а также советы и рекомендации по настройке MS Excel, ошибки в работе программы, решение задач по импорту/экспорту документов, ссылки и обсуждение надстроек для Excel, обучающие материалы и т.п. размещаем в теме MS Excel - настройка, импорт/экспорт.
Если решение задачи не требует применения формул и макросов (VBA), то это обсуждение будет перенесено в тему по настройкам.

Все пожелания по принципам размещения сообщений по этим темам рассматриваются - пишите в личку Andrey_k.

[ДЫСЫпы]

Цитата: Сообщение от Amberaya Помогите, пожалуйста

Пожалуйста!

Сварганил по быстрому одно из решений, все очень просто, поразбирайте пример, примените к своим нуждам.
Не забудьте включить макросы

Скачать здесь

[Alvaddem]

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

[andrey_k]

Цитата: Сообщение от Alvaddem Формулы удобны когда фиксированное количество данных, например 500 строк, а если варьируются, то опять нужно вставлять или копировать формулы под изменившиеся данные. При написании макроса этой проблемы нет.

кто мешает использовать формулы поиска по всему столбцу - вида ВПР($F$1$;$A:$D;2;ложь) ?
На медленной машине при количестве строк несколько тысяч/десятков тысяч может чуть подтормаживать пересчет, но здесь, видимо, поисковых запросов немного, так что можно пренебречь.
Я обычно использую в таблицах исходных данных видимое ограничение - заполняю последнюю-предпоследнюю строку текстом вида "END", и в ссылках использую эту строку в качестве "нижней" строки поиска.
При добавлении новых данных в таблицу с исходниками вставляю всегда строки выше этой строки-маркера, и все поисковые функции автоматически меняются.

Макрос хорош, когда пользователи совсем уж неграмотные (что даже корректно строки добавить не могут), способ обработки стабилен и часты повторяющиеся действия, как в рассматриваемом примере про печать 500 одинаковых бланков по разным людям.

[Alvaddem]

For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
If ....... Then
For k = 1 To Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row
Sheets(2).Cells(k,y)= Cells(i,1.... и т.д.)
.................................................. ....
Next k
End If
Next i
и никаких формул не нужно.

[DmitriC]

Народ, а никто не подскажет, что за хрень с выполнением функции СУММЕСЛИ?

Почему-то эта функция считает аргументы "1.1" и "1.1.15" (и только их) равными как в критерии поиска так и в диапазоне суммирования.
Вот файл, поясняющий мой вопрос: http://rghost.ru/6Ngw6W4YB

В данном файле функция в ячейке E5 суммирует содержимое ячеек B3 и B7, а должна, по-идее "подсчитывать" только содержимое ячейки B7. Аналогично, неверно работает функция, в ячейке E3.

[andrey_k]

Цитата: Сообщение от DmitriC Народ, а никто не подскажет, что за хрень с выполнением функции СУММЕСЛИ? Почему-то эта функция считает аргументы "1.1" и "1.1.15" (и только их) равными как в критерии поиска так и в диапазоне суммирования. Вот файл, поясняющий мой вопрос: http://rghost.ru/6Ngw6W4YB В данном файле функция в ячейке E5 суммирует содержимое ячеек B3 и B7, а должна, по-идее "подсчитывать" только содержимое ячейки B7. Аналогично, неверно работает функция, в ячейке E3.

Глюк подтверждаю. При этом на прямом сравнении ячеек (A=B) они различаются. Возможно, здесь внутренние поисковые заморочки СУММЕСЛИ - ведь она воспринимает запрос как поиск, в т.ч. работают шаблоны (? *).
Замена исходных строк на текстовые строки (1.1 стала ="1.1" и формат ячейки Общий, а не Текстовый, как у Вас) результат не изменил.
Попробуйте выравнять исходные аргументы по блокам (разделители цифр в числе): если вместо "1.1" поставить "1.1." - все работает правильно

[ДЫСЫпы]

Цитата: Сообщение от andrey_k Возможно, здесь внутренние поисковые заморочки

Попадалось на эту тему, глюк связан с преобразованием текста состоящего только из цифр, в число. И тут плучается 1.1=1.1.15=42005
Это же касается и функции Счетесли

Решается добавлением не цифры (если пробел, то спереди)

[DmitriC]

andrey_k, ДЫСЫпы
На самом деле (мне уже подсказали) глюк связан с тем, что Excel считает "1.1" и "1.1.15" датой. А поскольку сейчас 2015 год, то дата с "текущим годом" - 1.1 и дата "с 2015 годом" - 1.1.15 равны. Это легко проверить, изменив системную дату на, скажем, 2014 год. Тогда глюк проявится с 1.1.14. Но проблема в том, что Excel не хочет правильно обрабатывать эти самые "1.1" и "1.1.15", даже если задать формат ячеек как текстовый и даже, если перед указанными значениями поставить знак апострофа. Можно, конечно, поставить точки или пробелы, как Вы предлагаете, но в тех документах, где есть данный глюк с сотнями строк с такими значениями и десятками связанных между собой листов, это неприемлемо. Нужно искать другой способ.

[andrey_k]

Цитата: Сообщение от DmitriC но в тех документах, где есть данный глюк с сотнями строк с такими значениями и десятками связанных между собой листов, это неприемлемо. Нужно искать другой способ.

попробуйте другой заменитель, например, знак подчеркивания либо запятые, одиночные кавычки.
Понимаю, что у Вас, скорее всего, какая-то финансовая отчетность, и кодировка - номера статей ДДС и т.п. У самого похожая гадость была, но нам удалось убедить собственников компании, что это технические проблемы (очень помогла "аргументация" отдела IT, которые заявили, что им на разработку каждой формы отчета надо не менее 1,5 месяцев, а я эти изменения клепал каждые день-два в соответствии с пожеланиями владельца бизнеса): остались на Excel, но с легкой корректировкой этих кодов.

[DmitriC]

Цитата: Сообщение от andrey_k попробуйте другой заменитель, например, знак подчеркивания либо запятые, одиночные кавычки. Понимаю, что у Вас, скорее всего, какая-то финансовая отчетность, и кодировка - номера статей ДДС и т.п.

Насчет статей Вы правы, все именно так и есть. По-поводу заменителя, я пока думаю после номера статьи ставить точку:
1.1 -> 1.1.
1.1.15 -> 1.1.15.
Других вариантов у меня пока тоже нет. :\

А может, в техподдержке Майкрософта спросить?

[Alvaddem]

Напишите полностью функцию которую вы используете и что хотите получить в итоге!
Но вообще я для больших массивов пишу макросы. Просто и быстро.
Проблема ваша не велика. Функция СУММЕСЛИ суммирует числовые значения, а 1.1.15 никак числом быть не может. Если считаете значения то применяется СЧЁТЕСЛИ.

Добавлено через 28 минут
А, вообще ваша задача очень простая! Скиньте файл. Сделаю как вам нужно.

Добавлено через 1 час 44 минуты
1.1 7 1.1 7
1.1.1 5 1.1.3 3
1.1.2 4 1.1.15 2
1.1.3 3 1.1.16 10
1.1.15 2
1.1.16 10

Ваш пример у меня в Excel!!! Считает, как нужно. Есть такая проблема в Excel. Формулу обязательно нужно вводить Enter. Эту ошибку делают, когда вводится формула в начале копирования.
После Enter на E3 все рассчиталось правильно.

Добавлено через 2 часа 0 минут
Запятую не поставил в строке "После Enter, на E3 все рассчиталось правильно". Enter естественно на строке ввода формул!

[DmitriC]

Цитата: Сообщение от Alvaddem Напишите полностью функцию которую вы используете и что хотите получить в итоге!

Вы невнимательно читаете топик. Вот ссылка, которую я давал на XLS-файл, который считается неправильно: http://rghost.ru/6Ngw6W4YB
А это сообщение с первоначальным вопросом: http://nowa.cc/showpost.php?p=4931091&postcount=890
А это сообщение с анализом проблемы: http://nowa.cc/showpost.php?p=4931432&postcount=893
Там не все так просто.

Цитата: Сообщение от Alvaddem Функция СУММЕСЛИ суммирует числовые значения, а 1.1.15 никак числом быть не может.

В данном примере функция СУММЕСЛИ не суммирует 1.1.15. Она суммирует числовые значения.

[Alvaddem]

Зря обвиняете в невнимательности. Я скачал файл по ссылке. Ввел формулу, как я описал ранее и все прекрасно заработало. Если нужен мой файл с "правильной" формулой, то могу выслать. Без всяких заумей у меня-то работает!

Добавлено через 9 минут
На всякий случай даю ссылку на файл. Работает при всех раскладах.
https://drive.google.com/open?id=0Bx...OEU&authuser=0

[andrey_k]

Alvaddem,
описание задачи вполне четко сформулировано.
СУММЕСЛИ() - уникальная формула, поэтому спутать ее с другой невозможно, как и разобраться, что в диапазоне поиска искомого значения не обязательно должны быть числа, а любые значения.

Ваш пример недоступен - доступ дайте общий. Без этого Ваш пример остается неверифицируемым.


По супер-задаче поиска для любых таблиц?
Извольте, для примера.
Кодировка строк - 4 уровня, на каждом 5-20 позиций, так что не меньше 100 индивидуальных кодов нижнего уровня.
Кол-во исходных строк - от 2000. Столбцов в исходной таблице - не меньше 4, скорее всего 8-12. Таблиц таких - не меньше 12.
Уже исходный материал 2000*8*12=192000 ячеек. Разумеется, выборка по нему идет по каждому столбцу индивидуально.
Финальная таблица, полагаю, выглядит как строк 30-50 параметров. Скорее всего, соответствует всей таблице кодировке + группировка по каждому верхнему уровню (кстати, СУММЕСЛИ с подстановкой * прекрасно разом суммирует весь заданный подуровень). Получаем кол-во строк поиска для одного столбца (месяц/параметр) от 100 строк, в каждой из которых СУММЕСЛИ() ищет по исходному массиву, т.е. вариантов поиска 100*2000=200000.
С учетом годовой таблицы - 12 месяцев + 2-4 параметра по каждому месяцу - одна таблица со 12*4=48 столбцов только выборки.
К этому подитоги, сравнение столбцов/строк - в общем, стандартная стат.обработка.
К этому проверочные поиски на предмет ошибок кодировки, пропуска строк и т.п.
Получаем типичную таблицу работой только поисковых функций (=СУММЕСЛИ) на 48*200=9600 тыс. = 9,6 млн. запросов.
Если таблиц несколько (а обычно их не менее 3 только для одного предприятия), то поиск уже 9,6*3=28,8 млн. ячеек. Таблиц только главных 3, а если есть анализ по ЦФУ/ЦФО, то там рост количества таблиц пропорционально структуре ЦФО и необходимой аналитике.

Остальное DmitriC добавит

В общем, Excel 2003 на Pentium4 2800 пересчет делал от 10 мин (Core2Duo 2 на Excel 2007 с его многопоточностью считал около 7 мин.), а когда надо было сразу по холдингу, то компьютер перегревался и останавливался.

Программирование макросами очевидно облегчает задачу, но когда структура таблиц регулярно (не реже раза в две недели, обычно - каждый день) изменяется - программировать не успеваешь.

[Alvaddem]

Изменил уровень доступа для файла!
По функции я написал не про диапазон, а об суммировании. Суммирует естественно только числа.
Ну ежели структура меняется постоянно, тогда конечно формулы.