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

Вход

Регистрация

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

 

= Мир MS Excel/Формула ВПР и ПРАВСИМВ - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула ВПР и ПРАВСИМВ (Формулы/Formulas)
Формула ВПР и ПРАВСИМВ
Habilis Дата: Понедельник, 25.03.2024, 10:32 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

2016
Здравствуйте. Подскажите как решить такую задачу. Прохожу курс на Степик. Рассчитайте кол-во сотрудников, у которых отсутствует информация о принадлежности к какому-либо отделу. Есть и подсказка С помощью функций ПРАВСИМВ и ДЛСТР находим id сотрудника. Далее с помощью функции ЗНАЧЕН преобразуем текстовое значение id сотрудника в числовое и используем функцию ВПР. Подсчитать кол-во сотрудников с отсутствием информации об отделе можно несколькими способами: просто применяя фильтрацию, используя функцию ЕСЛИОШИБКА или СЧЁТЕСЛИ. Вроде не дурак но даже не понимаю с чего начать. Файл прикрепляю :-)
К сообщению приложен файл: funkcii2.xlsx (13.9 Kb)


SKond
 
Ответить
СообщениеЗдравствуйте. Подскажите как решить такую задачу. Прохожу курс на Степик. Рассчитайте кол-во сотрудников, у которых отсутствует информация о принадлежности к какому-либо отделу. Есть и подсказка С помощью функций ПРАВСИМВ и ДЛСТР находим id сотрудника. Далее с помощью функции ЗНАЧЕН преобразуем текстовое значение id сотрудника в числовое и используем функцию ВПР. Подсчитать кол-во сотрудников с отсутствием информации об отделе можно несколькими способами: просто применяя фильтрацию, используя функцию ЕСЛИОШИБКА или СЧЁТЕСЛИ. Вроде не дурак но даже не понимаю с чего начать. Файл прикрепляю :-)

Автор - Habilis
Дата добавления - 25.03.2024 в 10:32
AlexM Дата: Понедельник, 25.03.2024, 10:50 | Сообщение № 2
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4513
Репутация: 1128 ±
Замечаний: 0% ±

Excel 2003
Код
=ЕСЛИОШИБКА(ВПР(--ПСТР(A2;5;9);E$2:F$35;2;0);"")

Код
=СУММПРОИЗВ(--(СЧЁТЕСЛИ(E2:E35;--ПСТР(A2:A30;5;9))=0))
К сообщению приложен файл: funkcii2_1.xlsx (14.5 Kb)



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


Сообщение отредактировал AlexM - Понедельник, 25.03.2024, 10:54
 
Ответить
Сообщение
Код
=ЕСЛИОШИБКА(ВПР(--ПСТР(A2;5;9);E$2:F$35;2;0);"")

Код
=СУММПРОИЗВ(--(СЧЁТЕСЛИ(E2:E35;--ПСТР(A2:A30;5;9))=0))

Автор - AlexM
Дата добавления - 25.03.2024 в 10:50
Habilis Дата: Понедельник, 25.03.2024, 10:56 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

2016
Алексей большое вам спасибо. Сейчас буду разбираться :-)


SKond
 
Ответить
СообщениеАлексей большое вам спасибо. Сейчас буду разбираться :-)

Автор - Habilis
Дата добавления - 25.03.2024 в 10:56
Habilis Дата: Понедельник, 25.03.2024, 11:04 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

2016
Алексей ну это высший пилотаж. Для меня это пока сложно. :(


SKond
 
Ответить
СообщениеАлексей ну это высший пилотаж. Для меня это пока сложно. :(

Автор - Habilis
Дата добавления - 25.03.2024 в 11:04
AlexM Дата: Понедельник, 25.03.2024, 11:07 | Сообщение № 5
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4513
Репутация: 1128 ±
Замечаний: 0% ±

Excel 2003
Еще вариант формулы для второй задачи
Код
=СУММ(--(ЧАСТОТА(E2:E35;--ПСТР(A2:A30;5;9))=0))



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеЕще вариант формулы для второй задачи
Код
=СУММ(--(ЧАСТОТА(E2:E35;--ПСТР(A2:A30;5;9))=0))

Автор - AlexM
Дата добавления - 25.03.2024 в 11:07
Habilis Дата: Понедельник, 25.03.2024, 11:09 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

2016
Буду разбираться. Большое вам спасибо.


SKond
 
Ответить
СообщениеБуду разбираться. Большое вам спасибо.

Автор - Habilis
Дата добавления - 25.03.2024 в 11:09
NikitaDvorets Дата: Понедельник, 25.03.2024, 11:16 | Сообщение № 7
Группа: Авторы
Ранг: Ветеран
Сообщений: 557
Репутация: 126 ±
Замечаний: 0% ±

Excel 2019
Habilis, + вариант с 3-мя шагами.
К сообщению приложен файл: ew_otbor_sotrudnikov_25_03_202.xlsx (17.8 Kb)
 
Ответить
СообщениеHabilis, + вариант с 3-мя шагами.

Автор - NikitaDvorets
Дата добавления - 25.03.2024 в 11:16
Habilis Дата: Понедельник, 25.03.2024, 13:41 | Сообщение № 8
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

2016
Никита большое вам спасибо. Я так и думал что есть подвох. И он оказался. Нужно было сформировать новый столбец Наличие/отсутствие отдела. И уже в нём работать. Теперь всё становится ясно :)

С уважением и благодарностью С.Кондулуков


SKond
 
Ответить
СообщениеНикита большое вам спасибо. Я так и думал что есть подвох. И он оказался. Нужно было сформировать новый столбец Наличие/отсутствие отдела. И уже в нём работать. Теперь всё становится ясно :)

С уважением и благодарностью С.Кондулуков

Автор - Habilis
Дата добавления - 25.03.2024 в 13:41
AlexM Дата: Понедельник, 25.03.2024, 15:02 | Сообщение № 9
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4513
Репутация: 1128 ±
Замечаний: 0% ±

Excel 2003
Habilis, Если в строке формул, в формуле выделить фрагмент СЧЁТЕСЛИ(E2:E35;--ПСТР(A2:A30;5;9)) и нажать F9 вы увидите массив нулей и единиц, который у NikitaDvorets формирует столбец С



Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.
 
Ответить
СообщениеHabilis, Если в строке формул, в формуле выделить фрагмент СЧЁТЕСЛИ(E2:E35;--ПСТР(A2:A30;5;9)) и нажать F9 вы увидите массив нулей и единиц, который у NikitaDvorets формирует столбец С

Автор - AlexM
Дата добавления - 25.03.2024 в 15:02
_Boroda_ Дата: Понедельник, 25.03.2024, 15:17 | Сообщение № 10
Группа: Модераторы
Ранг: Местный житель
Сообщений: 16675
Репутация: 6481 ±
Замечаний: 0% ±

2003; 2007; 2010; 2013 RUS
СЧЁТЕСЛИ(E2:E35;--ПСТР(A2:A30;5;9))
Алексей, в СЧЁТ(СУММ)ЕСЛИ(МН) аргументы автоматически преобразуются в числа (если это возможно), поэтому "--" можно не писать
Кстати, поэтому Счётесли (и прочие подобные) некорректно работает с, например, банковскими счетами (20 символов) - цифры после 15-го знака обнуляются


Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
Сообщение
СЧЁТЕСЛИ(E2:E35;--ПСТР(A2:A30;5;9))
Алексей, в СЧЁТ(СУММ)ЕСЛИ(МН) аргументы автоматически преобразуются в числа (если это возможно), поэтому "--" можно не писать
Кстати, поэтому Счётесли (и прочие подобные) некорректно работает с, например, банковскими счетами (20 символов) - цифры после 15-го знака обнуляются

Автор - _Boroda_
Дата добавления - 25.03.2024 в 15:17
AlexM Дата: Понедельник, 25.03.2024, 15:38 | Сообщение № 11
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4513
Репутация: 1128 ±
Замечаний: 0% ±

Excel 2003
_Boroda_, спасибо.



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

Автор - AlexM
Дата добавления - 25.03.2024 в 15:38
Habilis Дата: Понедельник, 25.03.2024, 15:56 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

2016
Ребята всем большое спасибо. Я ещё только на берегу безграничного океана под названием Excel.


SKond
 
Ответить
СообщениеРебята всем большое спасибо. Я ещё только на берегу безграничного океана под названием Excel.

Автор - Habilis
Дата добавления - 25.03.2024 в 15:56
Habilis Дата: Понедельник, 25.03.2024, 18:20 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

2016
Ребята всем большое спасибо. Что-то не въезжаю я в это ВПР. Вот задача на ту же тему. Нужно рассчитать среднюю цену товаров в магазине во время распродажи. Если у товара не указана скидка, то цена распродажи будет равна цене до распродажи. Ответ округлите до 1 знака после запятой. Вот файл. Если бы в левом столбце просто стояли бы цифры 1 2 3 4 то проблем бы не было. При помощи ВПР подтянул бы скидку в 3-й столбец и узнал бы цену товара. После этого вычислил бы среднее значение. В левом столбце стоит Продукт 1. Преподаватель говорит что С помощью функции ПРАВСИМВ или инструмента ТЕКСТ ПО СТОЛБЦАМ находим номер товара. Далее с помощью функции ВПР подтягиваем %скидки с таблицы на листе "скидка". После рассчитываем новую цену, если у товара не указана скидка, значит цена будет соответствовать цене до распродажи. Данное условие можно применить с помощью функции ЕСЛИОШИБКА. Средняя цена товаров - это средняя цена столбца "Цена распродажи". И это бы я сделал при помощи функции ПСТР потом бы перевёл её в число. Но столбик то должен быть слева вот в чём дело. В общем куда ни кинь всюду клин. Подскажите как решить эту задачу. Буду очень благодарен :)
К сообщению приложен файл: funkcii3.xlsx (14.4 Kb)


SKond
 
Ответить
СообщениеРебята всем большое спасибо. Что-то не въезжаю я в это ВПР. Вот задача на ту же тему. Нужно рассчитать среднюю цену товаров в магазине во время распродажи. Если у товара не указана скидка, то цена распродажи будет равна цене до распродажи. Ответ округлите до 1 знака после запятой. Вот файл. Если бы в левом столбце просто стояли бы цифры 1 2 3 4 то проблем бы не было. При помощи ВПР подтянул бы скидку в 3-й столбец и узнал бы цену товара. После этого вычислил бы среднее значение. В левом столбце стоит Продукт 1. Преподаватель говорит что С помощью функции ПРАВСИМВ или инструмента ТЕКСТ ПО СТОЛБЦАМ находим номер товара. Далее с помощью функции ВПР подтягиваем %скидки с таблицы на листе "скидка". После рассчитываем новую цену, если у товара не указана скидка, значит цена будет соответствовать цене до распродажи. Данное условие можно применить с помощью функции ЕСЛИОШИБКА. Средняя цена товаров - это средняя цена столбца "Цена распродажи". И это бы я сделал при помощи функции ПСТР потом бы перевёл её в число. Но столбик то должен быть слева вот в чём дело. В общем куда ни кинь всюду клин. Подскажите как решить эту задачу. Буду очень благодарен :)

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

Excel 2003
скидка
Код
=ЕСЛИОШИБКА(ВПР(--ПРАВБ(A2;2);скидка!A$2:B$9;2;0);0)

Цена распродажи
Код
=B2*(1-C2)

Среднее
Код
=СРЗНАЧ(D2:D12)

Что-то не въезжаю я в это ВПР

Функция ВПР - это одна из поисковых функций. Она используется для выполнения вертикального поиска значения в крайнем левом столбце таблицы или массива и возвращает значение, которое находится в той же самой строке в столбце с заданным номером.
К сообщению приложен файл: funkcii3_1.xlsx (14.8 Kb)



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


Сообщение отредактировал AlexM - Понедельник, 25.03.2024, 18:58
 
Ответить
Сообщениескидка
Код
=ЕСЛИОШИБКА(ВПР(--ПРАВБ(A2;2);скидка!A$2:B$9;2;0);0)

Цена распродажи
Код
=B2*(1-C2)

Среднее
Код
=СРЗНАЧ(D2:D12)

Что-то не въезжаю я в это ВПР

Функция ВПР - это одна из поисковых функций. Она используется для выполнения вертикального поиска значения в крайнем левом столбце таблицы или массива и возвращает значение, которое находится в той же самой строке в столбце с заданным номером.

Автор - AlexM
Дата добавления - 25.03.2024 в 18:49
Habilis Дата: Понедельник, 25.03.2024, 19:27 | Сообщение № 15
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

2016
Алексей большое вам спасибо. Мне всё таки простительно. 64 года. А код интересный. Обязательно покопаюсь.


SKond
 
Ответить
СообщениеАлексей большое вам спасибо. Мне всё таки простительно. 64 года. А код интересный. Обязательно покопаюсь.

Автор - Habilis
Дата добавления - 25.03.2024 в 19:27
AlexM Дата: Понедельник, 25.03.2024, 20:23 | Сообщение № 16
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4513
Репутация: 1128 ±
Замечаний: 0% ±

Excel 2003
Мне всё таки простительно. 64 года.
Вы всего на два года младше. :)



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


Сообщение отредактировал AlexM - Понедельник, 25.03.2024, 22:34
 
Ответить
Сообщение
Мне всё таки простительно. 64 года.
Вы всего на два года младше. :)

Автор - AlexM
Дата добавления - 25.03.2024 в 20:23
Habilis Дата: Понедельник, 25.03.2024, 21:42 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

2016
А сколько лет вы занимаетесь Экселем?


SKond
 
Ответить
СообщениеА сколько лет вы занимаетесь Экселем?

Автор - Habilis
Дата добавления - 25.03.2024 в 21:42
AlexM Дата: Понедельник, 25.03.2024, 22:33 | Сообщение № 18
Группа: Друзья
Ранг: Участник клуба
Сообщений: 4513
Репутация: 1128 ±
Замечаний: 0% ±

Excel 2003
сколько лет вы занимаетесь Экселем
сложный вопрос до Excel был Суперкальк под DOS. Плотнее наверно с 2004, а на этом форуме учусь и помогаю более 11,5 лет.
Excel для меня появился в Windows 3.11. Это примерно 1995 год



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


Сообщение отредактировал AlexM - Понедельник, 25.03.2024, 22:38
 
Ответить
Сообщение
сколько лет вы занимаетесь Экселем
сложный вопрос до Excel был Суперкальк под DOS. Плотнее наверно с 2004, а на этом форуме учусь и помогаю более 11,5 лет.
Excel для меня появился в Windows 3.11. Это примерно 1995 год

Автор - AlexM
Дата добавления - 25.03.2024 в 22:33
Habilis Дата: Вторник, 26.03.2024, 10:05 | Сообщение № 19
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

2016
Ну вы уже ветеран в Эксель. А я только начинаю этот путь. До этого занимался сайтами, html,css3. Пробовал заниматься и созданием анимаций After Effekts. Но не зашло. Экселем занимаюсь где-то полгода. Купил курс на Степике и изучаю. Путаюсь пока в сложных формулах но это пока.


SKond
 
Ответить
СообщениеНу вы уже ветеран в Эксель. А я только начинаю этот путь. До этого занимался сайтами, html,css3. Пробовал заниматься и созданием анимаций After Effekts. Но не зашло. Экселем занимаюсь где-то полгода. Купил курс на Степике и изучаю. Путаюсь пока в сложных формулах но это пока.

Автор - Habilis
Дата добавления - 26.03.2024 в 10:05
Habilis Дата: Четверг, 28.03.2024, 13:12 | Сообщение № 20
Группа: Пользователи
Ранг: Новичок
Сообщений: 12
Репутация: 0 ±
Замечаний: 0% ±

2016
Всем добрый день. Большое спасибо за участие и поддержку. Вчера я закончил базовый курс Эксель. Естественно я рад. Хоть какую-то опору под ногами получил. А сейчас в каком направлении мне двигаться дальше. Excel это Океан и я хочу плыть дальше. Буду благодарен за видео и курсы :D

С уважением С.Кондулуков.


SKond
 
Ответить
СообщениеВсем добрый день. Большое спасибо за участие и поддержку. Вчера я закончил базовый курс Эксель. Естественно я рад. Хоть какую-то опору под ногами получил. А сейчас в каком направлении мне двигаться дальше. Excel это Океан и я хочу плыть дальше. Буду благодарен за видео и курсы :D

С уважением С.Кондулуков.

Автор - Habilis
Дата добавления - 28.03.2024 в 13:12
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Формула ВПР и ПРАВСИМВ (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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