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

Вход

Регистрация

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

 

= Мир MS Excel/Как формулу "ВПР" заставить выдать 2-е решение - Мир MS Excel

Регистрация · Логин: · Пароль: · · Забыли пароль?
Страница 1 из 11
Модератор форума: _Boroda_, Pelena, Manyasha, SLAVICK 
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как формулу "ВПР" заставить выдать 2-е решение (Формулы/Formulas)
Как формулу "ВПР" заставить выдать 2-е решение
Пытливый Дата: Четверг, 15.09.2016, 11:54 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 28
Репутация: 0 ±
Замечаний: 80% ±

Excel 2010
Здравствуйте, у меня есть некая таблица с данными по персоналу (Книга 1 вкладка "Удостоверения").
На основании этого списка мне надо составить протоколы проверки знаний (Книга 1 вкладка "Протоколы по ОТ").
Зная что формула "ВПР" ищет данные только в крайнем левом столбце таблицы я:
1) Составил формулу которая показывает "нужные №" протоколов (Удостоверения!A);
2) Составил формулу "ВПР" (Протоколы по ОТ!B15:E29) которая находит "нужный №" (Удостоверения!A) совпадающий с № (Протоколы по ОТ!M2) протокола и выводит имя работника(Удостоверения!B).
Но только 1-го в из "нужных".
Вопрос: "Как заставить формулу увидеть остальных работников из протокола, или для этих целей можно использовать другую формулу?"
Макросы не понимаю и не знаю как применять...
К сообщению приложен файл: 4734163.xls(87Kb)
 
Ответить
СообщениеЗдравствуйте, у меня есть некая таблица с данными по персоналу (Книга 1 вкладка "Удостоверения").
На основании этого списка мне надо составить протоколы проверки знаний (Книга 1 вкладка "Протоколы по ОТ").
Зная что формула "ВПР" ищет данные только в крайнем левом столбце таблицы я:
1) Составил формулу которая показывает "нужные №" протоколов (Удостоверения!A);
2) Составил формулу "ВПР" (Протоколы по ОТ!B15:E29) которая находит "нужный №" (Удостоверения!A) совпадающий с № (Протоколы по ОТ!M2) протокола и выводит имя работника(Удостоверения!B).
Но только 1-го в из "нужных".
Вопрос: "Как заставить формулу увидеть остальных работников из протокола, или для этих целей можно использовать другую формулу?"
Макросы не понимаю и не знаю как применять...

Автор - Пытливый
Дата добавления - 15.09.2016 в 11:54
Karataev Дата: Четверг, 15.09.2016, 13:03 | Сообщение № 2
Группа: Проверенные
Ранг: Ветеран
Сообщений: 837
Репутация: 312 ±
Замечаний: 0% ±

Excel
Вариант с допстолбцом (лист Протокол, столбец V) и формулой массива (вводится Ctrl+Shift+Enter) (V3):
Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(Удостоверения!$A$1:$A$5000=$M$1;СТРОКА(Удостоверения!$A$1:$A$5000);"");СТРОКА(1:1));"")

Формула массива просматривает на листе Удостоверения 5 тыс строк.
Также есть формулы на листе Протокол, которые ссылаются на допстолбец.
Допстолбец можно скрыть.
К сообщению приложен файл: 9839684.xls(86Kb)


 
Ответить
СообщениеВариант с допстолбцом (лист Протокол, столбец V) и формулой массива (вводится Ctrl+Shift+Enter) (V3):
Код
=ЕСЛИОШИБКА(НАИМЕНЬШИЙ(ЕСЛИ(Удостоверения!$A$1:$A$5000=$M$1;СТРОКА(Удостоверения!$A$1:$A$5000);"");СТРОКА(1:1));"")

Формула массива просматривает на листе Удостоверения 5 тыс строк.
Также есть формулы на листе Протокол, которые ссылаются на допстолбец.
Допстолбец можно скрыть.

Автор - Karataev
Дата добавления - 15.09.2016 в 13:03
Karataev Дата: Четверг, 15.09.2016, 13:23 | Сообщение № 3
Группа: Проверенные
Ранг: Ветеран
Сообщений: 837
Репутация: 312 ±
Замечаний: 0% ±

Excel
Или вариант без формулы массива, но с двумя допстолбцами. Один допстолбец в протоколе в столбце V, другой допстолбец в удостоверениях в столбце G.
В протоколе можно не делать допстолбец, но тогда в столбцах будут более громоздкие формулы.
К сообщению приложен файл: 4734163-.xls(86Kb)


 
Ответить
СообщениеИли вариант без формулы массива, но с двумя допстолбцами. Один допстолбец в протоколе в столбце V, другой допстолбец в удостоверениях в столбце G.
В протоколе можно не делать допстолбец, но тогда в столбцах будут более громоздкие формулы.

Автор - Karataev
Дата добавления - 15.09.2016 в 13:23
Nic70y Дата: Четверг, 15.09.2016, 21:40 | Сообщение № 4
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3923
Репутация: 830 ±
Замечаний: 0% ±

Excel 2013
Как формулу "ВПР" заставить
см. лист "Удостоверения"
К сообщению приложен файл: 5985156.xls(86Kb)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)


Сообщение отредактировал Nic70y - Четверг, 15.09.2016, 21:41
 
Ответить
Сообщение
Как формулу "ВПР" заставить
см. лист "Удостоверения"

Автор - Nic70y
Дата добавления - 15.09.2016 в 21:40
Пытливый Дата: Пятница, 16.09.2016, 11:38 | Сообщение № 5
Группа: Пользователи
Ранг: Новичок
Сообщений: 28
Репутация: 0 ±
Замечаний: 80% ±

Excel 2010
Karataev, 1й вариант так себе, а второй имеет место быть.
Но протокол то у нас не один, народу то больше 1000 человек...
А формула в столбце "G" (Удостоверения) упорно не хочет работать с 2-мя и более протоколами (будет больше 100 протоколов)


Сообщение отредактировал Пытливый - Пятница, 16.09.2016, 11:49
 
Ответить
СообщениеKarataev, 1й вариант так себе, а второй имеет место быть.
Но протокол то у нас не один, народу то больше 1000 человек...
А формула в столбце "G" (Удостоверения) упорно не хочет работать с 2-мя и более протоколами (будет больше 100 протоколов)

Автор - Пытливый
Дата добавления - 16.09.2016 в 11:38
Пытливый Дата: Пятница, 16.09.2016, 11:44 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 28
Репутация: 0 ±
Замечаний: 80% ±

Excel 2010
Nic70y, Решение не плохое, но работает только с одним протоколом.
т.е указываю в столбце "F" (Удостоверения) номера протоколов "2", "3" - распознает как единый массив и прет всех в каждый протокол (протоколов будет больше сотни)
К сообщению приложен файл: 1119768.xls(94Kb)
 
Ответить
СообщениеNic70y, Решение не плохое, но работает только с одним протоколом.
т.е указываю в столбце "F" (Удостоверения) номера протоколов "2", "3" - распознает как единый массив и прет всех в каждый протокол (протоколов будет больше сотни)

Автор - Пытливый
Дата добавления - 16.09.2016 в 11:44
Karataev Дата: Пятница, 16.09.2016, 11:49 | Сообщение № 7
Группа: Проверенные
Ранг: Ветеран
Сообщений: 837
Репутация: 312 ±
Замечаний: 0% ±

Excel
Внес изменения, чтобы работало с несколькими протоколами. В удостоверениях в столбце "A" нужно указыать номер протокола.
Формула для допстолбца "G" в удостоверениях:
Код
=ЕСЛИ(A5<>"";A5&"_"&СЧЁТЕСЛИ($A$5:A5;A5);"")

Формула для допстолбца "V" в протоколе:
Код
=ЕСЛИОШИБКА(ПОИСКПОЗ($M$1&"_"&СТРОКА(1:1);Удостоверения!G:G;0);"")
К сообщению приложен файл: 2322938.xls(86Kb)




Сообщение отредактировал Karataev - Пятница, 16.09.2016, 11:50
 
Ответить
СообщениеВнес изменения, чтобы работало с несколькими протоколами. В удостоверениях в столбце "A" нужно указыать номер протокола.
Формула для допстолбца "G" в удостоверениях:
Код
=ЕСЛИ(A5<>"";A5&"_"&СЧЁТЕСЛИ($A$5:A5;A5);"")

Формула для допстолбца "V" в протоколе:
Код
=ЕСЛИОШИБКА(ПОИСКПОЗ($M$1&"_"&СТРОКА(1:1);Удостоверения!G:G;0);"")

Автор - Karataev
Дата добавления - 16.09.2016 в 11:49
Пытливый Дата: Пятница, 16.09.2016, 12:01 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 28
Репутация: 0 ±
Замечаний: 80% ±

Excel 2010
Karataev, Файл не могу приложить, но;
1) Формула для допстолбца "G" в удостоверениях - шедевр!
2) Формула для допстолбца "V" в протоколе - храмает.
Я скопировал строки с протоколом ниже, присвоил протоколу №2, на что формула мне выдала тех-же людей.
 
Ответить
СообщениеKarataev, Файл не могу приложить, но;
1) Формула для допстолбца "G" в удостоверениях - шедевр!
2) Формула для допстолбца "V" в протоколе - храмает.
Я скопировал строки с протоколом ниже, присвоил протоколу №2, на что формула мне выдала тех-же людей.

Автор - Пытливый
Дата добавления - 16.09.2016 в 12:01
Karataev Дата: Пятница, 16.09.2016, 12:06 | Сообщение № 9
Группа: Проверенные
Ранг: Ветеран
Сообщений: 837
Репутация: 312 ±
Замечаний: 0% ±

Excel
У Вас на на листе "Протокол" будет несколько протоколов, которые будут находиться один под другим?
Сделал, как понял. После копирования первого протокола вниз, нужно изменить формулы в столбце V:
1) нужно указать ячейку с номером протокола
2) нужно чтобы в функции СТРОКА было так: СТРОКА(1:1)
К сообщению приложен файл: 2322938-.xls(96Kb)




Сообщение отредактировал Karataev - Пятница, 16.09.2016, 12:10
 
Ответить
СообщениеУ Вас на на листе "Протокол" будет несколько протоколов, которые будут находиться один под другим?
Сделал, как понял. После копирования первого протокола вниз, нужно изменить формулы в столбце V:
1) нужно указать ячейку с номером протокола
2) нужно чтобы в функции СТРОКА было так: СТРОКА(1:1)

Автор - Karataev
Дата добавления - 16.09.2016 в 12:06
Пытливый Дата: Пятница, 16.09.2016, 13:09 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 28
Репутация: 0 ±
Замечаний: 80% ±

Excel 2010
Karataev, Огромное спасибо! Вопрос решен.
Сейчас буду сочинять протоколы...
 
Ответить
СообщениеKarataev, Огромное спасибо! Вопрос решен.
Сейчас буду сочинять протоколы...

Автор - Пытливый
Дата добавления - 16.09.2016 в 13:09
Nic70y Дата: Пятница, 16.09.2016, 21:27 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 3923
Репутация: 830 ±
Замечаний: 0% ±

Excel 2013
но работает только с одним
это моя невнимательность.
категорически настаиваю на моем решении, т.к. тема называется
Цитата
Как формулу "ВПР" заставить
:)
К сообщению приложен файл: 7311300.xls(94Kb)


ЯД(poison) 41001841029809
+7 978 049 98 74 (мтс)


Сообщение отредактировал Nic70y - Пятница, 16.09.2016, 21:28
 
Ответить
Сообщение
но работает только с одним
это моя невнимательность.
категорически настаиваю на моем решении, т.к. тема называется
Цитата
Как формулу "ВПР" заставить
:)

Автор - Nic70y
Дата добавления - 16.09.2016 в 21:27
Пытливый Дата: Понедельник, 19.09.2016, 09:02 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 28
Репутация: 0 ±
Замечаний: 80% ±

Excel 2010
Nic70y, Ваш вариант более компактен. (что лучше для меня...)
Буду пробовать с вашим вариантом.
 
Ответить
СообщениеNic70y, Ваш вариант более компактен. (что лучше для меня...)
Буду пробовать с вашим вариантом.

Автор - Пытливый
Дата добавления - 19.09.2016 в 09:02
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Как формулу "ВПР" заставить выдать 2-е решение (Формулы/Formulas)
Страница 1 из 11
Поиск:

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