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

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

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

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

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

[The_Prist]

Формула массива:
Для второго

=ИНДЕКС($A$1:$A$13;НАИМЕНЬШИЙ(ЕСЛИ($A$1:$A$13<>"";СТРОКА($A$1:$A$13));2))

Для шестого

=ИНДЕКС($A$1:$A$13;НАИМЕНЬШИЙ(ЕСЛИ($A$1:$A$13<>"";СТРОКА($A$1:$A$13));6))

$A$1:$A$13 - диапазон Ваших данных.
Формулы массива - завершать ввод в ячейку не просто нажатием Enter, а сочетанием клавиш Ctrl+Shift+Enter. Формула автоматически будет заключена в фигурные скобки - {}

Ссылка на файл пример - Скачать файл

[andrey_k]

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

По аналогии с рекомендацией The_Prist:

$A$1:$A$13 - диапазон Ваших данных.

$B$1=СЧИТАТЬПУСТОТЫ($A$1:$A$13)
тогда второе значение найдется по формуле НАИМЕНЬШИЙ($A$1:$A$13;$B$1+2),
тогда шестое значение найдется по формуле НАИМЕНЬШИЙ($A$1:$A$13;$B$1+6).

Возможно, для корректной работы функции НАИМЕНЬШИЙ потребуется присвоить пустым ячейкам значение 0. В этом случае подсчет количества пустых ячеек надо будет делать функцией СЧЁТЕСЛИ($A$1:$A$13;0).

[The_Prist]

Цитата: Сообщение от andrey_k $B$1=СЧИТАТЬПУСТОТЫ($A$1:$A$13) тогда второе значение найдется по формуле НАИМЕНЬШИЙ($A$1:$A$13;$B$1+2), тогда шестое значение найдется по формуле НАИМЕНЬШИЙ($A$1:$A$13;$B$1+6).

Не согласен, т.к. в данном случае функция НАИМЕНЬШИЙ:
во-первых - будет возвращать числа не по порядковому номеру в столбце, а по возрастанию;
во-вторых: даже для возврата второго значения($B$1+2) функция вернет #ЧИСЛО, не найдя девять числовых констант.

[andrey_k]

Цитата: Сообщение от The_Prist Не согласен, т.к. в данном случае функция НАИМЕНЬШИЙ: во-первых - будет возвращать числа не по порядковому номеру в столбце, а по возрастанию; во-вторых: даже для возврата второго значения($B$1+2) функция вернет #ЧИСЛО, не найдя девять числовых констант.

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

Так что придется людям учиться использовать функции массива :-)

[The_Prist]

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

Да, тупанул я. Вы действительно указали на возможные проблемы с пустыми ячейками. Извиняюсь.

[Mikhname]

andrey_k, по вашему не получается. Все равно спасибо за участие. The_Prist, спасибо большое. Круто! С массивами почти не приходилось работать, буду знать.

[guryi]

Добрый день. Подсакжите, пожалуйста. Как сделать, чтобы в таблице я перемножаю столбцы, но в одном столбце я писал не цифру а слово (например, ок или да).

[Alan2006]

Цитата: Сообщение от guryi Добрый день. Подсакжите, пожалуйста. Как сделать, чтобы в таблице я перемножаю столбцы, но в одном столбце я писал не цифру а слово (например, ок или да).

Не совсем понятно что должно быть результатом в этом случае...
Можно сделать проверку.... типа Если(И(Эточисло(Аргумент1), ЭтоЧисло(Аргумент2)), Аргумент1*Аргумент2, "Оба аргумента должны быть числом")

Могу не помнить только как конкретно функции называются в ехеле... но подход примерно такой...

[andrey_k]

Цитата: Сообщение от guryi Добрый день. Подсакжите, пожалуйста. Как сделать, чтобы в таблице я перемножаю столбцы, но в одном столбце я писал не цифру а слово (например, ок или да).

Точнее формулируйте задачу.
Слово должно заменяться каким-то числом при перемножении или, наоборот, в зависимости от результата перемножения должно формироваться это слово?

[rexmist]

Цитата: Сообщение от andrey_k Точнее формулируйте задачу. Слово должно заменяться каким-то числом при перемножении или, наоборот, в зависимости от результата перемножения должно формироваться это слово?

=если(A1="Да";1*B1;2*B1)

[SuperFly]

Здравствуйте, подскажите можно ли автоматизировать заливку ячеек, ну или постановку в них знака?
Например есть горизонтальные 24 ячейки, нужно выделить каждую 6 ячейку, а в другом случае каждую 8 или 12 и т.д.

[profik777]

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

меню "формат" -"условное форматирование" -формула =И(1=1;(ЦЕЛОЕ(СТОЛБЕЦ(A1)/3)=СТОЛБЕЦ(A1)/3))
(потом перезайти и убрать кавычки)
кнопка формат-вид -выбираем заливку
затем копируем формат на весь лист

Пример тут: http://exfile.ru/download/87205

[InFreeBSD]

Интересная у меня задачка, хотелось бы узнать ваше мнение....

Есть таблица Excel, левая крайняя колонка - идентификационный код человека (во всех моих таблицах является уникальным индексом для функции ВПР() )
вторая колонка - ФИО
третья колонка - номер месяца (1-12)
следующие колонки - данные, которые мне нужно извлекать

До настоящего момента на каждого человека у меня не более одной строки и индекс является уникальным, в функции ВПР - значение интервальный просмотр - "ЛОЖЬ", но данных настолько много, что не вмещаются в 255 колонок Excel 2003 (по различным причинам не использую 2007), поэтому хочу изменить структуру таким образом, чтоб на каждого человека припадало по 12 строк(по одной на каждый месяц), но в таком случае не знаю как извлекать данные -( Мне нужна такая функция, похожая на ВПР, но чтоб критерий поиска индекса был в два этапа, первый - идентификационный номер человека, второй - номер месяца. я еще не до конца понимаю значение слова "интервальный просмотр", но мне кажется он в данной ситуации не поможет, т.к. отличное от значения "ЛОЖЬ" даст приближенное значение (ближайшую по значению строку), что меня не устраивает...

Буду рад любым подсказкам ..

С ув. Олег.

[andrey_k]

Цитата: Сообщение от InFreeBSD Интересная у меня задачка, хотелось бы узнать ваше мнение.... Есть таблица Excel, левая крайняя колонка - идентификационный код человека (во всех моих таблицах является уникальным индексом для функции ВПР() ) вторая колонка - ФИО третья колонка - номер месяца (1-12) следующие колонки - данные, которые мне нужно извлекать До настоящего момента на каждого человека у меня не более одной строки и индекс является уникальным, в функции ВПР - значение интервальный просмотр - "ЛОЖЬ", но данных настолько много, что не вмещаются в 255 колонок Excel 2003 (по различным причинам не использую 2007), поэтому хочу изменить структуру таким образом, чтоб на каждого человека припадало по 12 строк(по одной на каждый месяц), но в таком случае не знаю как извлекать данные -( Мне нужна такая функция, похожая на ВПР, но чтоб критерий поиска индекса был в два этапа, первый - идентификационный номер человека, второй - номер месяца. я еще не до конца понимаю значение слова "интервальный просмотр", но мне кажется он в данной ситуации не поможет, т.к. отличное от значения "ЛОЖЬ" даст приближенное значение (ближайшую по значению строку), что меня не устраивает... Буду рад любым подсказкам .. С ув. Олег.

Как я понял, Вы хотите инфу разместить на разных листах соответственно по месяцам?
Тогда поиск будет в два этапа - найти название листа по номеру месяца и на этом листе в обычном режиме найти нужную запись.
Я такое делаю сочетанием функций ДВССЫЛ и ВПР.

Кстати, по опыту проверено: для большого массива (от 1,5-3 тыс элементов) в качестве поисковой функции вместо ВПР/ГПР быстрее работает СУММЕСЛИ. Если искомые данные - числа, и поле для поиска единственное в массиве, то ускорение будет в разы. Также нет необходимости обрабатывать ошибку Н/Д - СУММЕСЛИ просто выводит 0. И если Вы будете менять таблицу (вставлять/удалять столбцы/строки), то в ВПР надо вручную отслеживать ссылку на столбец, а СУММЕСЛИ с ее прямым указанием столбца изменит все сама.

[The_Prist]

Можно использовать двойной проход той же ВПР. Можно использовать формулы массива. Нужно видеть пример исходных данных и то, чего хочется получить в итоге. Тогда проще будет Вам помочь.

Ладно, не хотите выкладывать пример - смотрите мой.
1 Вариант:
Формула массива(завершается нажатием клавиш Ctrl+Shift+Enter)

=--ПОДСТАВИТЬ(ВПР(E1&F1;$A$1:$A$6&$B$1:$C$6;2;0);E1;" ")

E1&F1 - сцепка ФИО и номер месяца
$A$1:$A$6&$B$1:$C$6 - диапазон значений - сцепка ФИО, номер месяца и столбец искомых значений.

2 Вариант:
Обычная формула.

=СУММПРОИЗВ((I2=$A$1:$A$6)*(J2=$B$1:$B$6);$C$1:$C$ 6)

Для большей понятливости лучше скачать файл

P.S. есть и еще варианты, но думаю Вам хватит и этого...