Доброго времени суток! Нужен совет. Предположим, в столбце А есть список имён (вида Имя Фамилия), причём в некоторых ячейках людей может быть несколько, разделённых точкой с запятой (Иванов Иван; Алексеев Алексей). Так как имён много, столбец получается слишком большой ширины (а переносить текст в несколько строк нельзя). Хотелось бы сделать так, чтобы при сужении столбца до размеров одного имени-фамилии остальной текст (люди) в ячейке изменялись в таком виде:
вместо Иванов Иван; Алексеев Алексей было что-то типа: Иванов Иван +1
или
Иванов Иван...
Заранее благодарю за любые советы!

gling, спасибо за помощь! Дополнительный вопрос: если мне необходимо будет добавить другие рейтинги к среднему значению, как тогда изменить формулу? Я фактически разобрался, однако проблема именно в том факте, что формула не видит наименования товаров, если они имеют вид формулы. Я прикрепил файл примера (упрощённый); у меня есть формула типа =СРЗНАЧ(ЕСЛИОШИБКА(1/(СЧЁТЕСЛИ(D2;"*"&$A$1:$A$4762&"*")*$B$1:$B$4762)^-1;"");B2) - задача в том, что нужно найти общее среднее значение нескольких аргументов, которые могут добавляться:
ЕСЛИОШИБКА(1/(СЧЁТЕСЛИ(D2;"*"&$A$1:$A$4762&"*")*$B$1:$B$4762)^-1;"") - 1-й аргумент
B2 - второй аргумент
могут быть третьи и четвёртые, и нужно найти именно среднее значение всех (где в начале бы стояла формула срзнач). Заранее благодарю!

Доброго времени суток! Столкнулся с трудностями при попытке объединить несколько рейтинговых значений в одну ячейку (найти итоговое среднее значение). В столбце А указаны имена и поставщики товара в таком виде: А1: Товар 1 (Поставщик 4) А2:А5: *описание товара*. В столбце В - базовый рейтинг товара (рейтинг №1), в столбце D - заказы на товары (по типу Товар 1; Товар 4 - в одной ячейке может быть сразу несколько товаров), в следующих столбцах для каждого товара указан класс (через него в таблице 1 вычисляется рейтинг №2) и цена (через неё в таблице 2 вычисляется рейтинг №3). Задача в следующем: найти средний рейтинг для каждого заказа. То есть, если в заказе есть Товар 1 и Товар 3, в итоговой ячейке должно быть среднее значение:
- Рейтинга №1 Товара 1 (указан рядом с товаром)
- Рейтинга №1 Товара 2 (указан рядом с товаром)
- Рейтинга №2 Товара 1 (вычисляется на основании класса товара в таблице 1)
- Рейтинга №2 Товара 2 (вычисляется на основании класса товара в таблице 1)
- Рейтинга №3 Товара 1 (вычисляется на основании цены товара в таблице 2)
- Рейтинга №3 Товара 2 (вычисляется на основании цены товара в таблице 2)
Я пробовал через ВПР, но когда в ячейке заказов несколько значений, подсчёт не удаётся осуществить. Кроме того, сами имена товаров являются результатом формулы с подстановкой знаков через &. Файл примера прилагаю. Заранее спасибо за помощь!

P.S. Возможно также возникают сложности из-за разности имён: в столбце А имена имеют вид: Товар 1 (Поставщик 4), а в столбце заказов указан только тип товара: Товар 1. Может, нужно добавить символ * или что-то ещё...
Р.P.S. Также, полагаю, сложности из-за того, что имя товара - это формула вида: ="Товар 1"&" (Поставщик "&СЛУЧМЕЖДУ(1;&")". (В оригинале, конечно, есть ссылка на список поставщиков).

Доброго времени суток! Возникла небольшая проблемка с расчётом средней цены. Итак, в столбце А расположены имена товаров через строку (А2 - Товар 1, А3 - описание Товара 1, А4 - Товар 2 и т.д.), в столбце В - цена (также через строку); в столбцах С и D расположено количество купленного товара в магазине и онлайн; в столбце Е - имена заказанных товаров (причём в одной ячейке могут быть несколько товаров, разделённых точкой с запятой). Наконец, в столбце F - средняя цена заказанных товаров из столбца Е. Загвоздка в том, что имена самих товаров в столбце А имеют вид формулы (="Товар 1"&" ("&СУММ(C2:D2)&")") Получается так: Товар 1 (745) или Товар 4 (115). У меня есть формула для расчёта средней цены: =СРЗНАЧ(ЕСЛИОШИБКА(1/(СЧЁТЕСЛИ(E2;"*"&$A$1:$A$5000&"*")*$B$1:$B$5000)^-1;"")), но она выдаёт ошибку (деление на 0), так как не может найти Товар 1, если он указан как формула и с дополнением: Товар 1 (745). Нужно сделать так, чтобы имена товаров находились по приблизительному значению (то есть цена Товара 1 может быть обнаружена в ячейке, значение которой Товар 1 (745). Файл примера прилагаю. Заранее благодарю за ответы и помощь!

Доброго времени суток. Возникло небольшое затруднение при копировании макета для данных. Предположим, в ячейке А1 есть постоянное значение (например, 5000); в ячейках диапазона В1:В10 числа 1 или 0 (обозначающие наличие или отсутствие продажи), а в диапазоне С1:С10 простая формула вида =ЕСЛИ(B1=1;7500-A$1;""), где 7500 - цена товара. Необходимо скопировать эту табличку (А1:С10) дальше вниз несколько десятков раз, но при копировании значение ячейки А$1 не меняется: скопировав диапазон А1:С10 вниз значение С11 должно быть =ЕСЛИ(B11=1;7500-A$11;""), но оно равно =ЕСЛИ(B11=1;7500-A$1;""). Файл примера прилагаю. Заранее спасибо за любые ответы и помощь!

ПавелW, спасибо, всё работает!

Доброго времени суток! Возникли трудности с расчётом итоговой суммы товара. Итак, в столбце А указано имя товара (Товар 1, Товар 2 и т.д.), в столбце В - одно или несколько присутствующий у него дополнений (указанных через точку с запятой) вида "Дополнение 1; Дополнение 4". В столбце Е перечислены все типы дополнений, в столбце F - надбавки за их наличие, а в столбце G - штрафы за их отсутствие. Задача, в сущности, проста: в столбце С для каждого товара найти итог суммы всех надбавок за присутствующие дополнения и суммы всех штрафов за отсутствие остальных дополнений. Например: у Товара 1 есть Дополнение 1 и Дополнение 5; значит, в столбце С (итоге) должно быть число, равное разнице между суммой надбавок за дополнения 1 и 5 и и суммой штрафов за отсутствующие дополнения со 2-4 и с 6-10. Файл примера прилагаю. Заранее благодарю за любую помощь и ответы!

P.S. в столбцах Е (дополнения), F (надбавки) и G (штрафы) данные могут меняться и дополняться.

МатросНаЗебре, ПавелW, gling, благодарю, всё работает! Дополнительный вопрос: что, если мне нужно найти не сумму этих товаров в столбце Е, а среднее значение их стоимости? Как тогда переделать эти формулы?

Доброго времени суток! Возникли некоторые трудности с подсчётом значений, найденных через ВПР. Итак, в столбце А есть список артикулов товаров (типа 1_0001 или 2_0003); в столбце В - соответствующая стоимость этих товаров. В столбце D перечислены заказы товаров (в виде этих артикулов/названий), причём некоторые из заказов содержат несколько артикулов, разделённых точкой с запятой (типа 1_0001; 1_0003; 2_0001). Задача, по сути, простая - найти стоимость в столбце Е каждого заказа. Через ВПР (вместо таблицы выделяя просто столбцы А и В, так как между строками артикулов могут быть другие типы данных - возможно, описание товара) я могу найти стоимость каждого заказанного товара, но только если он один в ячейке. У меня не получается заставить ВПР подсчитать сумму всех заказанных позиций в одной ячейке, товары в которой разделены точкой с запятой. Файл примера прилагаю. Заранее спасибо за любые ответы и помощь!

Доброго времени суток! Возникли некоторые затруднения с созданием реестра товаров. Итак, есть список товаров и их сжатых изображений в таком виде:
А1: *имя_товара_1*    В1: *фото_товара_1*
А2: *имя_товара_2*    В2: *фото_товара_2*
(Изображения в столбце В - это сжатые картинки, вставленные прямо в лист книги, а не ссылки на изображения в отдельной папке или на веб-страницы. Да, я знаю, что книга будет много весить )
В столбце С указаны дни месяца, а в столбце D - имена некоторых товаров, которые лучше всего продавались/принесли больше прибыли (следующего вида):
*имя_товара_1*; *имя_товара_5*
*имя_товара_6*
*имя_товара_3*; *имя_товара_2*; *имя_товара_4*
*имя_товара_9*; *имя_товара_10*
*имя_товара_2*
*имя_товара_8*
(Да, в некоторые дни товаров может быть несколько - и превью нужно добавить ко всем)
Сам реестр очень длинный: наименований (и, соответственно, превью) больше сотни, а данные в столбце D простираются на тысячи строк. Кроме того, в ассортимент часто добавляются новые позиции, а данные в столбце D могут меняться и дополняться. Задача в том, чтобы, условно говоря, добавив в ячейку D2050 *имя_товара_9* excel автоматически сравнивал его с ассортиментом и добавлял примечание с превью товара - при наведении курсора на имя товара показывалось его изображение.
Файл примера прилагаю. Заранее спасибо за помощь!

Доброго времени суток! Есть проблема со сравнением значений времени. Предположим, что в диапазоне А1:А30 указаны часы работы (формат ячеек - время) в таком виде: 8:15:00; в ячейке А31 - сумма всех значений времени работы за месяц. Необходимо сравнить эту сумму со стандартом (например, 150 часов в месяц), и в зависимости от результата указать аргументы типа "Зачёт" и "Незачёт". Проблема в том, что excel переводит время в числа, меньше 1, и достаточно сложно запустить анализ выработки. Необходимо что-то типа такого:
ЕСЛИ(А31>=150:00:00;"Зачёт";"Незачёт")
Заранее спасибо!

Доброго времени суток! Возникла небольшая проблема с функцией ЕСЛИ (необходимо, чтобы в качестве ответа она выводила не 1 или 0, а спецсимвол, взятый из таблицы (Вставить - Символ)). Предположим, что в ячейке А1 есть меняющиеся числа, а в ячейке В1, в зависимости от величины этого числа (больше или меньше пороговых значений), необходимо проставить определённые знаки (например, символ звезды ☆). Проблема, как я понял, в том, что символы - это знаки юникода, а excel меняет мои аргументы на символы ANSI. Причём, даже если указать эти звёзды в скрытом столбце и потом в формуле ссылаться непосредственно на них, excel всё равно поменяет их (в данном случае - на символ "«"). Файл примера прилагаю. Заранее спасибо!

https://support.microsoft.com/ru-ru/office/%D1%84%D1%83%D0%BD%D0%BA%D1%86%D0%B8%D1%8F-%D1%81%D0%B8%D...

Доброго времени суток! Задача состоит в следующем: необходимо подсчитать число неправильно стоящих букв при сравнении двух слов. Например, в ячейке А2 указано "Яблоко", в ячейке В2 - "Яблако", а в ячейке С2 - 1, так как в В2 одна ошибка в слове (по сравнению с А2). И так для каждой строки: если в А3 и в В3 значения "Груша", то в С3 будет 0, так как ошибок нет. Надеюсь, понятно объяснил. Слова могут различаться по длине. Файл примера прилагаю.

Доброго времени суток! Есть небольшая задачка, связанная с отображением времени. Мне необходимо перевести числовые значения секунд в текстовый формат времени, т.е. число "46 333 961" должно быть отображено как 1 год, 5 месяцев, 18 дней, 6 часов, 32 минуты, 41 секунда (желательно в одно ячейке). Такое вообще возможно сделать в excel? Заранее спасибо.

Доброго времени суток. У меня есть вопрос касательно размещения изображений. Если в диапазоне А1:Е5 (квадрат 5х5) размещено несколько изображений, наложенных друг на друга (подогнанных по размеру), существует ли способ их прокрутки, как в фотографиях Windows (от первого до последнего и наоборот)?

Доброго времени суток! Возникла небольшая проблемка. В тесте, который я создаю, возникает ошибка #ССЫЛКА! при перетаскивании значения в другую ячейку. Основная мысль вот в чём: есть задание (простой пример, типа 10+10*10), и окошко для ответа; в другой части листа есть ячейки с готовыми ответами, среди которых правильный. Нужно перенести саму ячейку в поле для ответа; после этого, если ответ верен (проверяется формулой ЕСЛИ с ответами в секретном столбце), рядом должна появиться единица. В конце теста все единицы суммируются и от них проставляется оценка. Загвоздка в том, что как бы я не переносил и не ссылался на ответ ученика, возникает эта ошибка. Прошу помочь!
P.S. Да, я знаю, что гораздо проще писать ответ вручную, и тогда его легко можно сравнить с правильным и поставить единицу или раскрасить, но мне необходимо сформировать тест именно с перетаскиванием ячейки. Надеюсь, способ есть...

Hugo, gling, спасибо большое, помогло!

Доброго времени суток! Возникла небольшая проблема с расчётом суммы через ВПР. Предположим, что каждый из 20 человек (имена которых указаны в диапазоне А2:А21) жертвует случайную сумму на одну из 5 целей (названия которых указаны в диапазоне B2:B21, а суммы - в диапазоне С2:С21). В диапазоне F2:F6 указана сумма цели (сбора средств). Задача в том, чтобы рядом (например, в диапазоне G2:G6) указать общую сумму собранных средств для каждой цели; иными словами, для цели А формула должна вытянуть все соответствующие значения в одну строку и суммировать их. Пока я придумал лишь громоздкий вариант через формулу ЕСЛИ, где расчёт занимает пять столбцов и 20 строк; а необходимо уместить это в одну ячейку (для каждой цели по одной ячейке). Файл примера прилагаю. Заранее спасибо!

Vik_tor, Павел \Ʌ/, спасибо большое!

Доброго времени суток! Возникла небольшая проблема. Мне требуется создать значения, равные случайному из списка, но не равные предыдущему. Например: в диапазоне Е1:Е5 есть пять названий фруктов; в столбце А1:А20 - случайные значения из столбца Е (через формулу ИНДЕКС и СЛУЧМЕЖДУ). Задача в том, чтобы в диапазоне С1:С20 также создать случайные значения из диапазона Е, но чтобы значения в одной строке не повторялись (если в А1 указано Яблоко, то в С1 может быть что угодно из диапазона Е, кроме яблока). Также, помимо одного столбца, нужно сделать так для нескольких (то есть, значения в столбцах С, D и далее в одной строке должны быть уникальными, хоть и взятыми случайно из диапазона Е). Надеюсь смог понятно объяснить Файл примера прилагаю. Заранее спасибо!

Доброго времени суток! Возникла небольшая проблемка с УФ. Итак, есть столбцы с числами; в каждой строчке (последующей ячейке) значения могут быть больше, меньше или равны предыдущему. Необходимо в каждом столбце выделить зелёным значения, которые больше предыдущего, и красным, если они меньше (если F3 равно 1351, а G3 - 812, то F3 окрашивается в красный. Далее - H3 равен 1428 (больше 812), соответственно, окрашивается в зелёный). Каждый столбец должен сравнивать значения предыдущего столбца (ячейки), и окрашивать свои ячейки в красный или зелёный (причём столбцы будут расширяться - как вниз, так и их количество, вправо). Значения равные предыдущему столбцу остаются без изменений. Файл примера прилагаю. Заранее спасибо!

БМВ, да, спасибо, всё получилось!

БМВ, теперь вообще ничего не работает

Metanor, БМВ, все эти способы работают только частично: перевод из списка удаляется в не зависимости от того, правильный ли он. Если я перевожу "Tea" как "Молоко", то из столбца удаляется "Молоко". При такой ошибке ничего не должно удаляться (а должно, только если перевод корректный). В любом случае, спасибо за помощь

Metanor, Sanim, я использую Excel 2019 (в нём нет формул ФИЛЬТР И ПРОСМОТРХ)

Доброго времени суток! Есть небольшая проблема. Итак, в столбце С есть слова на английском; если ввести правильный перевод рядом в столбец D, он станет зелёным. Однако в столбце G, в котором указаны все переводы (в перемешанном порядке) необходимо сделать так, чтобы соответствующие переводы слов исчезали после их ввода в столбец D. Например, если в С6 написано "Milk", а в D6 написать "молоко", то само слово "молоко" должно исчезнуть из столбца G (в какой бы строчке оно не находилось). Загвоздка в том, как уже говорилось, что позиции слов и их переводов не в одинаковых строчках. С формулами "подставить" и "заменить" что-то ничего не получилось. Файл примера прикрепляю.

Metanor, большое спасибо, всё получилось!

Доброго времени суток! Столкнулся с небольшой проблемкой при использовании УФ. Итак, в столбце А есть некоторые примеры вида 1+6 или 7+4; в столбце В - ответ, данный учеником. Задача проста: автоматически подсвечивать ячейки зелёным, если в них указан правильный ответ (и по возможности красным, если указан неверный). Я могу указать с помощью УФ правильный ответ для каждого примера, но делать это затруднительно (вручную при большом количестве примеров). Есть ли способ автоматизировать процесс? Как растянуть условное форматирование по столбцу В вниз? Файл примера прилагаю.

Приветствую, форумчане! Доброго времени суток! Столкнулся с затруднением при подсчёте людей в таблице. В столбце А есть список сотрудников в формате Имя Фамилия; в некоторых ячейках по несколько сотрудников, разделённых точкой с запятой (и также есть пустые ячейки в столбце). Мне необходимо подсчитать, сколько всего сотрудников в диапазоне (например, А1:А15) - причём не само количество людей, а уникальные значения (то есть, если в диапазоне пять раз встречается один человек, то формула не должна считать его пять раз подряд). Сейчас у меня в документе формула массива (с ДЛСТР и ПОИСКПОЗ), но она просто подсчитывает все значения в диапазоне, не проверяя их на уникальность.
Формула, которую я использую: =СУММ(ЕСЛИ(А1:А15="";0;ДЛСТР(А1:А15)-ДЛСТР(ПОДСТАВИТЬ(А1:А15;";";""))+1))
Я пользуюсь excel 2019 (нет функции УНИК). Образец таблицы прилагаю. Заранее спасибо!

Sanja, спасибо за помощь - макрос вроде работает. Но вот как его портировать в другие аналогичные документы? Мне ещё нужно было сделать такую же операцию, но список людей начинается с В2, а список уникальных имён и число повторений - в L2 и M2 соответственно. Я пробовал скопировать формулу, подправив буквы и цифры на нужные, и создал макрос, но в другой книге он не работает (хотя сохранение в формате с макросами есть). Я что-то делаю не так?