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

Вход

Регистрация

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

 

= Мир MS Excel/Поиск значений по столбцам внутри столбцов - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск значений по столбцам внутри столбцов (Формулы/Formulas)
Поиск значений по столбцам внутри столбцов
hijawarir Дата: Пятница, 28.06.2019, 17:57 | Сообщение № 1
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Всем доброго времени суток. Это мой первый топик здесь.
Задача: пользователь в отдельной ячейке (или в самой формуле) указывает нужный ему год и месяц в этом году, а затем уже пишет нужную ему формулу. Например, "какой % значение для Саши за май 2019 года занимает от суммы всех значений за май 2019 года" <- как найти % - это легко
Код
R11/SUM(R3:R11)
, а как отфильтровать именно май 2019 года, чтобы в дальнейшем уже оперировать только с нужным столбцом?
В файле с примером находится таблица с разбивкой по годам и месяцам, в реальной таблице, с которой приходится работать, этих годов over9000 и строк столько же, поэтому вручную копипастить данные по столбцам с одного листа на другой не вариант, соответственно прошу подсказать формулу.
P.S. Переименовать янв-дек в числа 1-12 не очень хороший метод
К сообщению приложен файл: 8374335.xls (30.0 Kb)


Сообщение отредактировал hijawarir - Пятница, 28.06.2019, 17:58
 
Ответить
СообщениеВсем доброго времени суток. Это мой первый топик здесь.
Задача: пользователь в отдельной ячейке (или в самой формуле) указывает нужный ему год и месяц в этом году, а затем уже пишет нужную ему формулу. Например, "какой % значение для Саши за май 2019 года занимает от суммы всех значений за май 2019 года" <- как найти % - это легко
Код
R11/SUM(R3:R11)
, а как отфильтровать именно май 2019 года, чтобы в дальнейшем уже оперировать только с нужным столбцом?
В файле с примером находится таблица с разбивкой по годам и месяцам, в реальной таблице, с которой приходится работать, этих годов over9000 и строк столько же, поэтому вручную копипастить данные по столбцам с одного листа на другой не вариант, соответственно прошу подсказать формулу.
P.S. Переименовать янв-дек в числа 1-12 не очень хороший метод

Автор - hijawarir
Дата добавления - 28.06.2019 в 17:57
AlexM Дата: Пятница, 28.06.2019, 18:18 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4448
Репутация: 1094 ±
Замечаний: 0% ±

Excel 2003
А16 - Имя
В16 - месяц
С16 - год
Код
=ИНДЕКС(B3:Y11;ПОИСКПОЗ(A16;A3:A11);ПОИСКПОЗ(C16;B1:Y1)+МЕСЯЦ(--(1&B16))-1)/СУММ(ИНДЕКС(B3:Y11;;ПОИСКПОЗ(C16;B1:Y1)+МЕСЯЦ(--(1&B16))-1))
К сообщению приложен файл: 8374335_01.xls (30.5 Kb)



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.


Сообщение отредактировал AlexM - Пятница, 28.06.2019, 20:41
 
Ответить
СообщениеА16 - Имя
В16 - месяц
С16 - год
Код
=ИНДЕКС(B3:Y11;ПОИСКПОЗ(A16;A3:A11);ПОИСКПОЗ(C16;B1:Y1)+МЕСЯЦ(--(1&B16))-1)/СУММ(ИНДЕКС(B3:Y11;;ПОИСКПОЗ(C16;B1:Y1)+МЕСЯЦ(--(1&B16))-1))

Автор - AlexM
Дата добавления - 28.06.2019 в 18:18
AlexM Дата: Пятница, 28.06.2019, 20:49 | Сообщение № 3
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4448
Репутация: 1094 ±
Замечаний: 0% ±

Excel 2003
Можно формулу сократить, заменив в двух местах фрагмент
ПОИСКПОЗ(C16;B1:Y1)+МЕСЯЦ(--(1&B16))-1
на
12*(C16-2018)+МЕСЯЦ(--(1&B16))

годов over 9000
Это как???



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.


Сообщение отредактировал AlexM - Пятница, 28.06.2019, 20:55
 
Ответить
СообщениеМожно формулу сократить, заменив в двух местах фрагмент
ПОИСКПОЗ(C16;B1:Y1)+МЕСЯЦ(--(1&B16))-1
на
12*(C16-2018)+МЕСЯЦ(--(1&B16))

годов over 9000
Это как???

Автор - AlexM
Дата добавления - 28.06.2019 в 20:49
_Boroda_ Дата: Пятница, 28.06.2019, 21:17 | Сообщение № 4
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
Можно формулу сократить, заменив в двух местах фрагмент
:D
--(1&B16)
на просто
1&B16
Функции дат (МЕСЯЦ, например) сами преобразуют подходящие текстовые значения в даты

Переименовать янв-дек в числа 1-12 не очень хороший метод

А написать там нормальные даты? (см. зеленые ячейки)
Тогда формулу Алексея можно так написать
Код
=ИНДЕКС(B3:Y11;ПОИСКПОЗ(A16;A3:A11);ПОИСКПОЗ(--(B16&C16);B2:Y2))/СУММ(ИНДЕКС(B3:Y11;;ПОИСКПОЗ(--(B16&C16);B2:Y2)))

Или такую написать
Код
=СУММПРОИЗВ(B3:Y11*(B2:Y2=--(B16&C16))*(A3:A11=A16))/СУММПРОИЗВ(B3:Y11*(B2:Y2=--(B16&C16)))

Но для больших объемов ИНДЕКСы значительно быстрее будут
К сообщению приложен файл: 8374335_03.xls (25.5 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
Можно формулу сократить, заменив в двух местах фрагмент
:D
--(1&B16)
на просто
1&B16
Функции дат (МЕСЯЦ, например) сами преобразуют подходящие текстовые значения в даты

Переименовать янв-дек в числа 1-12 не очень хороший метод

А написать там нормальные даты? (см. зеленые ячейки)
Тогда формулу Алексея можно так написать
Код
=ИНДЕКС(B3:Y11;ПОИСКПОЗ(A16;A3:A11);ПОИСКПОЗ(--(B16&C16);B2:Y2))/СУММ(ИНДЕКС(B3:Y11;;ПОИСКПОЗ(--(B16&C16);B2:Y2)))

Или такую написать
Код
=СУММПРОИЗВ(B3:Y11*(B2:Y2=--(B16&C16))*(A3:A11=A16))/СУММПРОИЗВ(B3:Y11*(B2:Y2=--(B16&C16)))

Но для больших объемов ИНДЕКСы значительно быстрее будут

Автор - _Boroda_
Дата добавления - 28.06.2019 в 21:17
hijawarir Дата: Воскресенье, 30.06.2019, 17:20 | Сообщение № 5
Группа: Пользователи
Ранг: Прохожий
Сообщений: 2
Репутация: 0 ±
Замечаний: 0% ±

Excel 2016
Благодарю Алекса и Бороду за ответ.
Уточняющие вопросы:
Как в формуле Бороды идёт поиск по годам?
Код
=ИНДЕКС(B3:Y11;ПОИСКПОЗ(A16;A3:A11);ПОИСКПОЗ(--(B16&C16);B2:Y2))/СУММ(ИНДЕКС(B3:Y11;;ПОИСКПОЗ(--(B16&C16);B2:Y2)))

Проверка вручную показала, что выдаётся неверный результат.

Подскажите, пожалуйста, как реализовать сразу два условия а)работа с целыми столбцами (а не отдельными ячейками на пересечении полей ИМЯ и МЕСЯЦ ГОДА) и б)дополнительная фильтрация по столбцу ТИП (см прикреплённый файл).
Пример задачи: каков прирост значений за май 2019 года по сравнению с апрелем 2018 года среди людей с ТИПОМ "весёлый"?
К сообщению приложен файл: 2683682.xls (30.0 Kb)
 
Ответить
СообщениеБлагодарю Алекса и Бороду за ответ.
Уточняющие вопросы:
Как в формуле Бороды идёт поиск по годам?
Код
=ИНДЕКС(B3:Y11;ПОИСКПОЗ(A16;A3:A11);ПОИСКПОЗ(--(B16&C16);B2:Y2))/СУММ(ИНДЕКС(B3:Y11;;ПОИСКПОЗ(--(B16&C16);B2:Y2)))

Проверка вручную показала, что выдаётся неверный результат.

Подскажите, пожалуйста, как реализовать сразу два условия а)работа с целыми столбцами (а не отдельными ячейками на пересечении полей ИМЯ и МЕСЯЦ ГОДА) и б)дополнительная фильтрация по столбцу ТИП (см прикреплённый файл).
Пример задачи: каков прирост значений за май 2019 года по сравнению с апрелем 2018 года среди людей с ТИПОМ "весёлый"?

Автор - hijawarir
Дата добавления - 30.06.2019 в 17:20
_Boroda_ Дата: Понедельник, 01.07.2019, 09:34 | Сообщение № 6
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16666
Репутация: 6478 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
hijawarir, Вы обратили внимание на то, что я написал
А написать там нормальные даты? (см. зеленые ячейки)
? Мой файл открывали? Формулы в зеленых ячейках смотрели?

Если по Вашему файлу, где шапка с текстовыми месяцами, то так можно (сдвинул данные на 2 столбца вправо, чтобы в столбцах А:В написать условия (кстати, добавил там в месяце и типе вып. списки)
Код
=СУММЕСЛИ(D:D;B16;ИНДЕКС(E:AB;;(B13-E1)*12+МЕСЯЦ(1&B15)))-СУММЕСЛИ(D:D;B16;ИНДЕКС(E:AB;;(B14-E1)*12+МЕСЯЦ(1&B15)))
К сообщению приложен файл: 2683682_2.xls (26.5 Kb)


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщениеhijawarir, Вы обратили внимание на то, что я написал
А написать там нормальные даты? (см. зеленые ячейки)
? Мой файл открывали? Формулы в зеленых ячейках смотрели?

Если по Вашему файлу, где шапка с текстовыми месяцами, то так можно (сдвинул данные на 2 столбца вправо, чтобы в столбцах А:В написать условия (кстати, добавил там в месяце и типе вып. списки)
Код
=СУММЕСЛИ(D:D;B16;ИНДЕКС(E:AB;;(B13-E1)*12+МЕСЯЦ(1&B15)))-СУММЕСЛИ(D:D;B16;ИНДЕКС(E:AB;;(B14-E1)*12+МЕСЯЦ(1&B15)))

Автор - _Boroda_
Дата добавления - 01.07.2019 в 09:34
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Поиск значений по столбцам внутри столбцов (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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