Excel впр формулы: Краткий справочник: функция ВПР — Служба поддержки Майкрософт

Содержание

Множественный ВПР() — выводим несколько значений по ключевому значению в MS EXCEL

history 30 сентября 2020 г.

Группы статей

file_download Файл примера

Функция ВПР() ищет заданное значение в ключевом столбце и выводит значение из соседнего столбца. Ключевой столбец в этом случае не содержит повторов. А что если содержит? Тогда функция выведет только первое встретившееся значение. Напишем формулу, которая выводит все значения, соответствующие искомому. Назовем эту формулу множественный ВПР().

Пусть дана исходная таблица с номерами заказов и товарами. Номера заказов могут повторяться.

У заказа 2 три повтора, в строке 3, 4 и 6. Перечень заказов не сортирован.

Выведем все наименования товаров для заказа 2, а также количество этих товаров.

Так как в ключевом столбце (Заказ) теперь несколько одинаковых значений, то функция ВПР() не годится — она выведет только самое первое, т. е. товар Манго. Чтобы вывести все 3 значения у заказа 2 создадим служебный столбец рядом с исходной таблицей.

Поместим в него формулу =СЧЁТЕСЛИ($B$8:B8;B8)

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

В таблице ниже, там где будем выводить товар и его количество для выбранного заказа, с помощью формулы

=СУММПРОИЗВ(($B$8:$B$14=$A$20)*($E$8:$E$14=A23)*(СТРОКА($B$8:$B$14)-СТРОКА($B$7)))

определим позиции повтора в исходной таблице (оранжевый столбец в нижней таблице). Это обычная формула (не формула массива).

Наименование товара выведем с помощью простой формулы =ИНДЕКС(C$8:C$14;$B23), аналогично выведем Количество.

Изменив в желтой ячейке номер заказа на 1, нижняя табличка изменится.

Покажем как работает формула =СУММПРОИЗВ(($B$8:$B$14=$A$20)*($E$8:$E$14=A23)*(СТРОКА($B$8:$B$14)-СТРОКА($B$7))) для заказа 1:

три выражения $B$8:$B$14=$A$20, $E$8:$E$14=A23 и СТРОКА($B$8:$B$14)-СТРОКА($B$7) порождают 3 массива чисел
первый массив — это номера позиций искомого заказа в исходной таблице. Выражение дает {ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ЛОЖЬ}, т.е.ИСТИНА соответствует номерам позиций 1 и 5 (выделено зеленым). Убедиться в этом просто — достаточно выделить выражение в строке формул и нажать клавишу F9
второе выражение дает массив {ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА}, который представляет номер повтора заказа (данный массив соответствует первому повтору). Номер повтора заказа из А23 (1) и из А24 (2) сравнивается номерами повторов заказов в столбце Е. Например, номер повтора 1 есть во многих строках: 1, 2, 3, 7 и часть из них не соответствует заказу №1. Это как раз и показывает массив — значение ИСТИНА стоит у элементов массива №№1, 2, 3, 7
произведение этих массивов дает множество, которое соответствует и номеру заказа (1) и его повтору в исходной таблице (1 в строке 23, и 2 в строке 24). Вот этот массив {1:0:0:0:0:0:0}, где 1 соответствует ИСТИНА. 1 получается перемножением 2-х ИСТИНА, все остальные комбинации ЛОЖЬ*ЛОЖЬ, ЛОЖЬ*ИСТИНА дают 0. Перемножение происходит попарно.
наконец, умножая предыдущий массив на массив порядковых номеров в исходной таблице получим только тот номер, который соответствует и номеру заказа и его повтору. Он будет только один, остальные в массиве будут равны 0. Вот этот массив для заказа 1 и его повтора 2: {0:0:0:0:5:0:0} Это 5-я позиция в исходной таблице.
Функция СУММПРОИЗ() складывает все элементы массива, т.к. ненулевой элемент заведомо только один, то функция отбросит все 0 и выведет позицию.

file_download Файл примера

O que é valor agregado?

Veja outros Сообщения

O que é valor agregado?

02.08.2018
Робсон Камарго

Um termo bastante falado, mas pouco utilizado na prática é o valor agregado, mas você sabe o que é valor agregado ? E por que as pessoas não utilizam?

Em linhas bem gerais podemos dizer que a técnicas de controle pela análise do valor agregado, é uma avaliação do que foi concluído vs o que foi planejado, ou seja, o que deveria estar pronto até a data da análise.

Mas para entender melhor o que isso significa é preciso associar ao valor agregado outros indices como valor planejado, variação de prazo, variação de custos e outros cálculos que servem também para fazer essa análise. Vou explicar mais logo abaixo.

O que é valor agregado: prazos, custos e desempenhos

Valor agregado em gerenciamento de projetos é um termo originário do mundo das finanças, que mede performance. É uma metodologia que integra a análise de escopo, cronograma e recursos financeiros em um projeto.

Mas a resposta para o valor agregado em projetos é bem diferente da pergunta o que é valor agregado de um produto, que pode ser explicada como a percepção que um cliente tem de um produto ou serviço que at Enda suas necessidades, рассмотрит необходимость seu benefício e preços em comparação à concorrência.

Na mesma linha, tambem pode ser confundida com a pergunta sobre или que é agregar valor, que nada mais é que acrescentar inovações, proporcionar um diferencial ao um produto que vai agradar mais os clientes em relação ao que já existse no mercado .

Então neste mesmo raciocínio, para entender como agregar valor ao product , é preciso saber como acrescentar Benefícios ou adicionar um plus ao produto que será entregue ao cliente.

Já na área de gerenciamento de projetos, o valor agregado é um recorte do que pode ser avaliado em um projeto que está em andamento. Для этого существуют 3 доблести, которые были разработаны с учетом следующих факторов: Доблесть Agregado, Доблесть Planejado и Custo Real.

Valor agregado é o valor do que já foi concluído, é quanto vale o que já foi realizado em seu projeto.

Valor planejado é quanto vale o que havia sido orçado até data da análise.

Custo Real é Quanto já gastou efetivamente no projeto.

Para explicar melhor, vamos dizer que quando um gerente de projetos recebe um projeto, ele tem um determinado orçamento, com uma programação específica de tarefas a serem cumpridas (escopo) e prazos de término.

Por instanceo, um projeto tem 10 meses para terminale uma verba total, com planejamento de gastos e tarefas mensais.

É claro que se ele é um gerente de projetos компетентный, que está semper atento ao que foi acordado com o cliente, vai supervisorar esses custos e prazos, para atingir satisfatoriamente os seus resultsados.

Para fazer isso, mais do que saber o que é valor agregado, ele vai precisar fazer a análise do que foi feito, ou seja, do valor agregado, em outras palavras, de quanto já concluiu, quanto já agregou de результатов 90 057 finalizados ао проекта.

O que é agregado valor: análise no projeto

Para fazer a valor agregado, que, inclusive, é a preocupação de muitos candidatos que vão fazer certificação PMP® (Project Management Professional), do PMI ® (Институт управления проектами), é preciso trabalhar com as três variáveis que citamos: o que havia sido planejado, o que foi realizado e o que foi gasto.

É assim que ele vai saber se está adiantado ou atrasado nos prazos e dentro do orçamento, economizando ou estourando a parte Financeira.

Пункт fazer essa análise a primeira coisa é reunir os seguintes dados:
. Общая доблесть в orçamento, chamado como Orçamento no Termino (ONT) ;
. Доблесть Planejado (VP) , que é o valor orçado para ser gasto até a data do status do projeto,
. Custo Real (CR) , que é o valor gasto efetivamente até data de staus,
. Доблесть Агрегадо (Вирджиния) , que é o valor de tudo o que foi concluído até data de status (baseado no valor orçado). Isso себе descobre multiplicando о процентный де conclusão да atividade pelo доблесть orçado да atividade. Somatória де todos esses valores até data de status é o valor agregado.

Com esses números será possível saber como o projeto está em termos de prazo e custos.

Prazo

Variação de prazo: Para medir como o projeto está em termos de prazo, o Primeiro Dado é avaliar a Variação de Prazo (VPr), ou seja, a diferença entre o quant o foi realizado em relação ao que tinha que ter sido realizado.

Для использования: Valor Agregado e Valor Planejado (VP), na seguinte equação:

VPR= VA – VP (доблесть Agregado menos доблесть Planejado).

Nesta conta, se o resultado for positivo, significa que está adiantado, já se o resultado for negativo, significa que está o projeto está atrasado. E se a variação for igual a 0, isso significa que o projeto está rigorosamente no prazo.

Индика де Десемпеньо де Празо: пункт saber qualé o percentual do que foi realizado, é preciso medir o ndice de Desempenho de Prazo (IDP), que é uma relação entre os dois fatores acima, Valor Agregado e Valor Planejado (VP) . Nesse caso, equação é a seguinte:

IDP= VA / VP (Valor Agregado dividido pelo Valor Planejado).

Nesta conta, se o resultado maior que 1, significa que or projeto está adiantado, já se o resultado for menor que 1, significa que está atrasado no volume de tarefas.

Observação: se IDP for > 1, o VPR será positivo (Adiantado). Se o IDP < 1, o VPR será negativo (Atrasado). Se o IDP for = 1, o VPR será ZERO, существенно, что проект является абсолютным, но не соответствует оригиналу.

Infelizmente, o mais comum é IDP < 1 ! Mas, vamos mudar esse cenário aprendendo como tomar melhor os projetos, certo?!

Custos

Mas assim como é preciso avaliar prazos, tambem é preciso investigar como está o andamento do projeto em termos de orçamento. E para isso, podemos calcular dois indicadores:

Variação de custos: O primeiro é a Variação de Custos (VC), onde vão ser usados o Valor Agregado menos o Custo Real (CR):

VC= VA – CR (Valor agregado menos Custo Real)

Aqui tambem se a variação é positiva existe economia no orçamento, enquanto se a variação for negativa, estaria estourando o orçamento.

Индикация Desempenho de Custos: A segunda conta é o IDC (Índice de Desempenho de Custos), которая относится к Valor Agregado и к количеству foi gasto ou o Custo Real (CR):

IDC= VA/ CR (Доблесть Agregado dividido pelo Custo Real).

E tambem se o IDC for igual a 1, estará dentro do orçamento ou On Budget.

Примечание: SE IDC для > 1, VC será positiva (Economizando). Se o IDC < 1, VC será negativa (Orçamento Estourado). Se o IDC for = 1, a VC será ZERO, significando que o projeto está absolutamente no Orçamento exato conforme o planejamento original, ou ON BUDGET como se diz na expressão americana.

Estimativa no Termino

Em cada reunião de status como cliente ouспонсор do projeto, o que eles querem saber фундаментальный é se o projeto vai terminar no prazo e dentro do orçamento. Quando isso acontece, maravilha…. mas nem semper isso é o que acontece.

Quando existsalguma variação nos prazos e nos custos, todo gerente de projeto vai precisar apresentar no Status Report uma nova estimativa, ou seja, quando o projeto será concluído ou, em termos de dinheiro, qual a nova estimativa Financeira.

Расчет новой финансовой оценки проекта:

Расчет новой финансовой оценки проекта, é preciso obter o orçamento inicial menos a variação de custo até o momento, ou seja, vai precisar utilizar o ONT (Orçamento o Итого) e VC (Variação de Custos).

ENT1= ONT- VC

Se houve, por instanceo, uma economia no que estava destinado ao Orçamento Total é preciso avaliar se ela foi pontual ou se essa mesma causa vai se propagar.

Caso seja pontual, a conta acima é o cálculo indicado. Mass se for se propagar, pode ser realizado um novo cálculo, que é o Orçamento no Término em relação ao Índice de Desempenho de Custo:

ENT2= ONT/ IDC

Mass se quiser saber Quanto a инда ву гастар пункт терминал о projeto, é possível fazer mais um cálculo.

Essa é a Estimativa para o Término (EPT), que deve ser calculada a partir de duas variações. Assim, исчисление Quanto ainda será gasto subtraindo a nova estimativa no término (ENT) do quanto já foi gasto, ou seja, utilizando a seguinte equação:

EPT1= ENT – CR

Mas será preciso avaliar, qual índice será apresentado: o cálculo a partir da nova estimativa no término que foi calculada a partir da variação pontual (ENT1) ou da variação que será propagada (ENT2).

Com essas формул é possível analisar или valor agregado e avaliar atrasos, economias ou projetos entregues dentro de prazos e orçamentos.

Espero que você possa utilizar ou testar nos seus projetos daqui pra frente.

Por que as pessoas não utilizam?

Por dois motivos: a primeira por desconhecimento; a segunda porque muitas vezes as pessoas não colocam os custos em cada atividade, e aí fica sem ter referência.

Mas tenho duas dicas que você pode utilizar:

— 1. Coloque R$ 1,00 por dia de duração da atividade.

— 2. Преобразование em horas, e use da mesma forma: PV: #horas planejadas; CR: #horas gastas, utilizadas; VA: % de conclusão multiplicado pela #de horas estimada para atividade. Экспериментируйте!

Para quem ainda tem dúvidas sobre o que é valor agregado e análise de valor agregado, no vídeo abaixo, eu dou examples práticos que vão clarear mais esse conceito, utilizando todas essas fó rmulas apresentadas aqui. 9ROBSON CAMARGO — Pro Джетос и Негосиос. Esse e todos os demais temas para todos os profissionais se tornarem Certificado PMP®.

Вход без сайта (WWW.robsoncamargo.com.br) и веха повестки дня, conteúdo do programa e apoio oferecido para os candidatos à certificação.

Владелец или автор

Робсон Камарго, PMP, MBA, GWCPM, ASF, профессор курсов MBA das Principais Escolas de Negócio do País: FGV, Fundação Dom Cabral и FIA/USP com Certificação PMP — Project Management Professional emitida пело PMI , MBA em Administração de Projetos pela FEA/USP e Мастер-сертификат Пела Джорджа Вашингтона. Робсон Камарго является автором книги PM VISUAL и автором метода PM VISUAL. Sua оборудовать реализовать treinamentos электронной Consultorias em empresas сделать Brasil и экстерьер. Робсон Камарго находится рядом с RC Робсон Камарго – Projetos e Negócios, с 11 января.

Под марками PMP, PMI, PMBOK и логотипом «REP» RegisteredEducationProvaider, зарегистрированным на торговой марке Project Management Institute, Inc.

Собре

Éuma empresa de Educação Corporativa oficialmente homologada pelo PMI com o selo de R.E.P. (Зарегистрированный поставщик образовательных услуг ), alinhada com o Triângulo de Talentos do Gerente de Projetos

Veja mais

Это 140 наиболее распространенных функций Excel с голландским переводом. Этот список очень полезен, если вы привыкли к английской версии Excel и работаете с голландской версией, которая принимает в формулах только голландские имена функций. Или наоборот!

Английский	Голландский
АБС	АБС
АДРЕС	АДРЕС
ОБЩАЯ ИНФОРМАЦИЯ	ОБЪЕДИНЕНИЕ
И	EN
АРАБСКИЙ	ARABISCH
СРЕДНИЙ	GEMIDDELDE
СРЕДНИЙIF	GEMIDDELDE.ALS
СРЕДНИЕЕСЛИ	GEMIDDELDEN.ALS
BAHTTEXT	BAHT.TEKST
BIN2DEC	BIN.N.DEC
BIN2HEX	BIN.N.HEX
ПОТОЛОК	AFRONDEN.BOVEN
CEILING.MATH	AFRONDEN.BOVEN.WISK
CELL	CEL
CHAR	TEKEN
ВЫБЕРИТЕ	KIEZEN
КОД	КОД
КОЛОННА	КОЛОМКА
КОЛОННА	КОЛОМНА
СЦЕПИТЬ	ТЕКСТ. SAMENV
СЦЕПИТЬ	ТЕКСТ.SAMENVOEGEN
ПРЕОБРАЗОВАТЬ Т	CONVERTEREN
COUNT	AANTAL
COUNTA	AANTALARG
COUNTBLANK 90 273	ААНТАЛ.ЛЕГЕ.ЦЕЛЛЕН
СЧЁТЕСЛИ	AANTAL.ALS
COUNTIFS	AANTALLEN.ALS
ДАТА 902 73	DATUM
DATEVALUE	DATUMWAARDE
ДЕНЬ	DAG
ДЕНЬ S	DAGEN
DEC2BIN	DEC.N.BIN
DEC2HEX	DEC.N.HEX
КОНМЕСЯЦ	ЛААТСТЕ.ДАГ
ЧЕТНЫЙ	ЧЕТНЫЙ
EXACT	GELIJK
FACT	FACULTEIT
FILTERXML	XML.FILTEREN
НАЙТИ	VIND.ALLES
ПОЛ	AFRONDEN. BENEDEN
FLOOR.MATH	AFRONDEN.BENEDEN.WISK
FORMULATEXT	FORMULETEKST 902 73
ЧАСТОТА	ИНТЕРВАЛ
HEX2BIN	HEX.N.BIN
HEX2DEC	HEX.N.DEC
HLOOKUP	HORIZ.ZOEKEN
ГИПЕРССЫЛКА	ГИПЕРССЫЛКА
ЕСЛИ	ALS
IFERROR	ALS.FOUT
IFS	ALS.VOORWAARDEN
ИНДЕКС	ИНДЕКС
НЕПРЯМОЙ	НЕПРЯМАЯ
ИНФО	ИНФО
INT	INTEGER
I SBLANK	ISLEEG
ISERROR	ISFOUT
ISEVEN	IS.EVEN
НЕЛОГИЧНЫЙ	ISLOGISCH
ISNA	ISNB
ISNONTEXT	ISGEENTEKST
IНОМЕР	ISGETAL
ISODD	IS. ONEVEN
ISOWEEKNUM	ISO.WEEKNUMMER
ISTEKST
БОЛЬШОЙ	GROOTSTE
ЛЕВЫЙ	Звенья
LEN	LENGTE
LOOKUP	ZOEKEN
LOWER	KLEINE.LETTERS
СПИЧКА	VERGELIJKEN
MAX	MAX
MAXIFS	MAX.ALS.VOORWAARDEN
СРЕДНЯЯ	СРЕДНЯЯ
СРЕДНЯЯ	DEEL
МИН	МИН
MINIFS	MIN.ALS.VOORWAARDEN
MOD	REST
MODE	MODUS
МЕСЯЦ	МАИ
MROUND	AFRONDEN.N.VEELVOUD
N	N
NA
NETWORKDAYS	NETTO. WERKDAGEN
NETWORKDAYS.INTL	NETWERKDAGEN.INTL
НЕ	NIET
СЕЙЧАС	NU
НЕЧЕТНЫЙ	ОДИН ЧЕТНЫЙ
СМЕЩЕНИЕ	ВЕРШУВИНГ
ИЛИ	ИЗ
ПРОЦЕНТИЛЬ	ПРОЦЕНТИЛЬ
ПРОЦЕНТИЛЬ.ИСКЛ 90 273	PERCENTIEL.EXC
PERCENTILE.INC	PERCENTIEL.INC
PERCENTIEL.INC	PERCENT.RANG
ПРОЦЕНТРАНГ.ИСКЛ.	ПРОЦЕНТРАНГ.ИСКЛ.
ПРОЦЕНТРАНГ.ВКЛ.	ПРОЦЕНТРАНГ.ВКЛ. 61	ИП	ИП
PMT	BET
Правильный	Начаты
2	777777777777737777377773737373773737737373773737777373773737373737377373737373737373737н. Quartile.exc	Kwartiel.exc
Quartile.inc	Kwartiel. inc
RAND	ASELECT
RANDBETWEEN	ASELECTTUSSEN
RANK	RANG
СРЕДНИЙ РАНГ	RANG.GEMIDDELDE
RANK.EQ	RANG.GELIJK
REPLACE	VERVANGEN
REPT	HERHALING
RIGHT	RECHTS
ROMAN	ROMEINS
КРУГЛЫЙ	АФРОНДЕН
КРУГЛЫЙ ВНИЗ	АФРОНДЕН.НААР.БЕНЕДЕН
КРУГЛЫЙ 902 73	АФРОНДЕН.НААР.БОВЕН
РЯД	RIJ
РЯД	RIJEN
ПОИСК	ВИНД.СПЕЦ
МАЛЫЙ	KLEINSTE
СТАНДОТКЛОН	СТАНДОТКЛОН
СТАНДОТКЛОН.П 902 73	СТАНДОТКЛОН.P
СТАНДОТКЛОН.S	СТАНДОТКЛОН. S
ЗАМЕНИТЬ	ЗАМЕНИТЬ
ПРОМЕЖУТОЧНЫЙ ИТОГ	ПРОМЕЖУТОЧНЫЙ ИТОГ
СУММ	СОМ
СУММЕСЛИ	СОМ.АЛС
СУММЕСЛИМН	СОММ.АЛ S
SUMPRODUCT	SOMPRODUCT
ПЕРЕКЛЮЧАТЕЛЬ	SCHAKELEN
ТЕКСТ	TEKST
TEXTJOIN	TEKST.COMBINEREN
СЕГОДНЯ	VANDAAG
TRANSPOS E	ТРАНСПОНЕРЕН
ТРИМ	SPATIES.WISSEN
UNICHAR	UNITEKEN
UNICODE	UNICODE
UPPER	HOOFDLETTERS
VLOOKUP	VERT.ZOEKEN
WEEKDAG
WEEKNUM	WEEKNUMMER
ГОД	JAAR
ONWAAR
TRUE	WAAR