Как в excel прайсе оставить только заказные позиции
Часто перед пользователями табличного редактора Эксель встает такая задача, когда необходимо удалить всю текстовую информацию в ячейках, а числовые данные оставить. Для осуществления этой процедуры существует множество способов. В статье мы детально разберем каждый метод, позволяющий оставить числа и убрать текст в ячейках.
Способы удаления текста и сохранения числовой информации
Представим, что у нас есть такая информация, располагающаяся в ячейке: «Было доставлено кусков мыла 763шт». Нам необходимо сделать так, чтобы осталось только значение 763 для проведения разнообразных математических операций. Хорошо, если нужно избавиться от текстовых данных только в одной ячейке, тогда можно реализовать удаление ручным способом, но этот вариант не подходит, когда в табличке находится слишком много разной информации. Здесь необходимо применять различные специальные функции табличного редактора.
Первый метод: использование специальной формулы
Для реализации этой процедуры можно применять специальную массивную формулу. Примерная формула выглядит так:
Разберем основные моменты:
- Специальную формулу необходимо вбивать в поле при помощи комбинации кнопок «Ctrl+Shift+Enter».
- Стоит заметить, что в таком виде массивная формула может использоваться только с текстовой информацией, в которой число знаков не больше 99. Для увеличения диапазона нужно, к примеру, заменить параметр «СТРОКА($1:$99)» на «СТРОКА($1:$200)». Иными словами, мы вместо показателя 99 вводим число знаков с запасом. Если ввести слишком большой диапазон, то обработка формулы может занять длительное время.
- Если в текстовых данных числовые значения разбросаны по всему тексту, то формула не сможет правильно обработать информацию.
Детально рассмотрим специальную массивную формулу на таком примере: «Было доставлено кусков мыла 763шт., а заказывали 780»
- В поле А1 располагается сама текстовая информация, из которой мы будем извлекать числовые данные.
- Фрагмент: МИН(ЕСЛИ(ЕЧИСЛО(-ПСТР(А1;СТРОКА($1:$99);1));СТРОКА($1:$99))) позволяет определить позицию 1-го значения в поле. Получаем значение 29.
- Фрагмент: ПРОСМОТР(2;1/ЕЧИСЛО(-ПСТР(А1;СТРОКА($1:$99);1));СТРОКА($1:$99)) позволяет определить позицию последнего значения в поле. Получаем значение 31.
- Мы получаем такую формулу: =ПСТР(А1;29;31-29+1). Оператор ПСТР позволяет извлечь из текстовой информации, указанной 1-м аргументом, начиная с заданной позиции (29) с числом знаков, заданным 3-м аргументом.
- В результате мы получаем:
=ПСТР(А1;29;31-29+1)- =ПСТР(А1;29;2+1)
- =ПСТР(А1;29;3)
- 763
Бывают ситуации обратные, когда нужно реализовать операцию извлечения односоставных текстовых данных, исключив числа.
Нам необходимо сохранить только текстовую информацию.
Специальная формула применяется по аналогичному алгоритму, что и вышерассмотренная. Она выглядит так: =ПСТР(А1;ПОИСК(«-«;А1)+1;ПОИСКПОЗ(ИСТИНА;ЕЧИСЛО(—ПСТР(ПСТР(А1;ПОИСК(«-«;А1)+1;999);СТРОКА($1:$99);1));0)-1)
Здесь мы, благодаря фрагменту ПОИСК(«-«;А1) отыскали локацию тире, а затем при помощи оператора ПОИСКПОЗ нашли в извлеченных текстовых данных позицию 1-го числа. Передали эти показатели в оператор ПСТР, который реализовал все дальнейшие преобразования.
Второй метод: использование специального макроса
Эту процедуру можно реализовать при помощи специального макроса, созданного в табличном редакторе Эксель. К примеру, у нас есть табличка, в которой существует колонка с текстовой информацией и числовыми данными. Нам нужно оставить только числовые данные, убрав при этом текст.
В табличном редакторе отсутствует интегрированная функция, поэтому нам необходимо создать такой пользовательский макрос:
Public Function GetNumbers(TargetCell As Range) As String
Dim LenStr As Long
For LenStr = 1 To Len(TargetCell)
Select Case Asc(Mid(TargetCell, LenStr, 1))
Case 48 To 57
GetNumbers = GetNumbers & Mid(TargetCell, LenStr, 1)
End Select
Next
End Function
Подробная инструкция по созданию пользовательского макроса выглядит так:
- Используя специальную комбинацию клавиш «Alt+F11», производим открытие редактора VBA. Альтернативный вариант – нажать ПКМ по рабочему листу и выбрать элемент «Исходный текст».
- Реализуем создание нового модуля. Для осуществления этой процедуры жмем левой клавишей мышки на элемент, имеющий наименование «Insert», а затем выбираем объект «Module».
- Производим копирование кода, который располагается выше, и вставляем его в созданный модуль. Копирование реализуем при помощи сочетания клавиш «Ctrl+C», а вставку – «Ctrl+V».
- Теперь в необходимой ячейке, в которой мы планируем вывести только числовую информацию, вбиваем такую формулу: =GetNumbers(А1).
- Нам нужно растянуть формулу вниз на все ячейки колонки. Для этого наводим указатель на нижний правый уголок ячейки. Курсор принял форму небольшого плюсика темного цвета. Зажимаем левую клавишу мышки и протягиваем формулу вниз до окончания таблички.
- Готово! Мы реализовали извлечение числовой информации при помощи специального макроса.
Заключение и выводы о процедуре извлечение
Мы выяснили, что существует несколько способов, позволяющих реализовать извлечение числовой информации. Осуществить эту операцию можно при помощи создания специальных макросов в редакторе VBA или же используя массивную формулу. Каждый пользователь может самостоятельно выбрать для себя наиболее удобный способ, который позволит ему убрать текстовую информацию из ячейки и оставить при этом числовые данные.
Добрый день! очень нужна ваша помощь гуру Excel .Имеется огромный прайс с картинками (несколько листов).Кроме наименований есть еще и характеристики.Как сделать что-бы при поставлении количества позиция(наименование, характеристика,цена,количество и сумма) попадала на отдельный лист?
Сформировать бланк заказа из прайс листа
Здравствуйте. Не особо сильна в терминологии, поэтому заранее прошу прощения. Задача следующая.
Сформировать лист с выбранными позициями из прайс листа
Здравствуйте! Суть вопрос следующая. Есть огромный прайс на 8000 позиций. Клиент скачивает прайс.
Составление прайс-листа на основе калькулятора
Добрый день. Составил я калькулятор со множеством переменных для разного тиража продукции. Прайс.
Обработка прайс листа для торговых площадок Копирование данных с одного листа на другой
Всем привет кто знает и может помогите Копирование данных с одного листа на другой обработка.
Как сделать что-бы при поставлении количества позиция(наименование, характеристика,цена,количество и сумма) попадала на отдельный лист?
Только прочитав в пятый раз до меня наконец то дошла суть вашего ребуса.
Пожалуйста учитывайте тот факт, что вы варитесь в своей проблеме и для вас она ясна и понятна.
Делать макросом однозначно.
Выполняться будет (мне кажется, будет быстрее и проще) только с ручного запуска макроса для текущего листа
И главный вопрос куда копировать?
Копироваться позиция должна на отдельный лист без картинок что-бы клиент мог отправлять мне только этот лист ,а не весь прайс .
Сразу возражения
1 книга с макросами - это гемор для не далёких пользователей. Всплывающая панелька Типа включите макросы как правило игнорируется. Соответственно толку от макроса нет ни какого.
2 Я не знаю какой антивирусник будет у твоего пользователя, но некоторые антивирусники (в недалёком прошлом, как сейчас - не знаю) отрицательно воспринимали тот факт что макрос что то там пытается сам создать и сохранить (а без этого все эти авто-формирования нового листа какие то не законченные)
3 на медленных машинах возможны проблемы со скоростью и пользователь может посчитать что ексель завис
я с вами согласен,но прайс весит много и клиенту не удобно его пересылать ,лучше конечно данную проблему решить с помощью формул, но, я так понимаю, что это сделать невозможно . Набивать заявку тоже проблематично,легче если весь заказ будет на отдельном листе что-бы можно было его загружать .
Тогда сложности для меня, при написании вашего макроса
1 я в пятницу только приехал из командировки и во вторник уезжаю опять. Как вы понимаете писать макрос дома мне нет ни какого желания.
2 там, куда я уеду, из развлечений только смотреть фильмы (заранее скаченные) или пьянствовать. Первое уже надоело, глазеть всё подряд уже мочи нет. Второе - накладно, да и здоровье дороже. Поэтому "пошевелить мозгами" хоть какое то, но развлечение.
3 Формирование "Заявки" будет происходить только при закрытии таблицы (по факту ввода количества - очень не рационально, т.к. необходимо отслеживать корректировку уже введённых данных, а это сложнее)
4 "Заявка" будет создана в том же месте где пользователь сохранит ваш прайс что не совсем удобно для пользователя, так как все привыкли открывать документ прямо из письма. Хотя можно и на рабочий стол выкинуть, как вариант.
5 Ваши таблицы не идентичны и в этом есть определённая сложность при анализе данных.
6 Представим что к концу месяца я отлажу все так как я себе представил.
7 Выложить на форуме будет большой проблемой. "Супер-скоростной" инет от билайна там такой что для входа в личный кабинет билайна требуется полчаса. Сколько потребуется для навигации по форуму и для выгрузки - большая и великая тайна.
Начиная с 2007-й версии функция удаления дубликатов является стандартной - найти ее можно на вкладке Данные - Удаление дубликатов (Data - Remove Duplicates) :
В открывшемся окне нужно с помощью флажков задать те столбцы, по которым необходимо обеспечивать уникальность. Т.е. если включить все флажки, то будут удалены только полностью совпадающие строки. Если включить только флажок заказчик, то останется только по одной строке для каждого заказчика и т.д.
Способ 2. Расширенный фильтр
Если у вас Excel 2003 или старше, то для удаления дубликатов и вытаскивания из списка уникальных (неповторяющихся) элементов можно использовать Расширенный фильтр (Advanced Filter) из меню (вкладки) Данные (Data) .
Предположим, что у нас имеется вот такой список беспорядочно повторяющихся названий компаний:
Выбираем в меню Данные - Фильтр - Расширенный фильтр (Data - Filter - Advanced Filter) . Получаем окно:
- Выделяем наш список компаний в Исходный диапазон (List Range) .
- Ставим переключатель в положение Скопировать результат в другое место (Copy to another location) и указываем пустую ячейку.
- Включаем (самое главное!) флажок Только уникальные записи(Uniqe records only) и жмем ОК.
Получите список без дубликатов:
Если требуется искать дубликаты не по одному, а по нескольким столбцам, то можно предварительно склеить их в один, сделав, своего рода, составной ключ с помощью функции СЦЕПИТЬ (CONCATENATE) :
Тогда дальнейшая задача будет сводиться к поиску дубликатов уже в одном столбце.
Способ 3. Выборка уникальных записей формулой
Чуть более сложный способ, чем первые два, но зато - динамический, т.е. с автоматическим пересчетом, т.е. если список редактируется или в него дописываются еще элементы, то они автоматически проверяются на уникальность и отбираются. В предыдущих способах при изменении исходного списка нужно будет заново запускать Расширенный фильтр или жать на кнопку Удаление дубликатов.
Итак, снова имеем список беспорядочно повторяющихся элементов. Например, такой:
Первая задача - пронумеровать всех уникальных представителей списка, дав каждому свой номер (столбец А на рисунке). Для этого вставляем в ячейку А2 и копируем затем вниз до упора следующую формулу:
=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;"")
В английской версии это будет:
=IF(COUNTIF(B$1:B2;B2)=1;MAX(A$1:A1)+1;"")
Эта формула проверяет сколько раз текущее наименование уже встречалось в списке (считая с начала), и если это количество =1, т.е. элемент встретился первый раз - дает ему последовательно возрастающий номер.
Для упрощения адресации дадим нашим диапазонам (например, исходя из того, что в списке может быть до 100 элементов) имена. Это можно сделать в новых версиях Excel на вкладке Формулы - Диспетчер имен (Formulas - Name manager) или в старых версиях - через меню Вставка - Имя - Присвоить (Insert - Name - Define) :
- диапазону номеров (A1:A100) - имя NameCount
- всему списку с номерами (A1:B100) - имя NameList
Теперь осталось выбрать из списка NameList все элементы имеющие номер - это и будут наши уникальные представители. Сделать это можно в любой пустой ячейке соседних столбцов, введя туда вот такую формулу с известной функцией ВПР (VLOOKUP) и скопировав ее вниз на весь столбец:
или в английской версии Excel:
=IF(MAX(NameCount)
Эта формула проходит сверху вниз по столбцу NameCount и выводит все позиции списка с номерами в отдельную таблицу:
Доброго дня местным форумчанам!
Нужна ваша помощь. Так сказать подсказать направление или помочь с реализацией.
Задача:
Из основного листа прайса, в дополнительный лист выводить только необходимую информацию по заказу, только по заказанным позициям.
Т.е. искать в определенном столбце непустое значение и выводить некоторые значения строки где эта непустая ячейка найдена.
Для примера.
Код - Наименование - Картинка - Заказ
1 - Название А - Картинка - ""
2 - Название Б - Картинка - 2
3 - Название В - Картинка - 1
Чтобы на втором листе были такие данные:
Код - Заказ
2 - 2
3 - 1
Подскажите какие функции использовать, пожалуйста.
Доброго дня местным форумчанам!
Нужна ваша помощь. Так сказать подсказать направление или помочь с реализацией.
Задача:
Из основного листа прайса, в дополнительный лист выводить только необходимую информацию по заказу, только по заказанным позициям.
Т.е. искать в определенном столбце непустое значение и выводить некоторые значения строки где эта непустая ячейка найдена.
Для примера.
Код - Наименование - Картинка - Заказ
1 - Название А - Картинка - ""
2 - Название Б - Картинка - 2
3 - Название В - Картинка - 1
Чтобы на втором листе были такие данные:
Код - Заказ
2 - 2
3 - 1
Подскажите какие функции использовать, пожалуйста.
Виолин
Нужна ваша помощь. Так сказать подсказать направление или помочь с реализацией.
Задача:
Из основного листа прайса, в дополнительный лист выводить только необходимую информацию по заказу, только по заказанным позициям.
Т.е. искать в определенном столбце непустое значение и выводить некоторые значения строки где эта непустая ячейка найдена.
Для примера.
Код - Наименование - Картинка - Заказ
1 - Название А - Картинка - ""
2 - Название Б - Картинка - 2
3 - Название В - Картинка - 1
Чтобы на втором листе были такие данные:
Код - Заказ
2 - 2
3 - 1
Подскажите какие функции использовать, пожалуйста.
Автор - Виолин
Дата добавления - 29.07.2014 в 18:14
При работе с таблицами Excel довольно часто приходится проводить отбор в них по определенному критерию или по нескольким условиям. В программе сделать это можно различными способами при помощи ряда инструментов. Давайте выясним, как произвести выборку в Экселе, используя разнообразные варианты.
Выполнение выборки
Выборка данных состоит в процедуре отбора из общего массива тех результатов, которые удовлетворяют заданным условиям, с последующим выводом их на листе отдельным списком или в исходном диапазоне.
Способ 1: применение расширенного автофильтра
Наиболее простым способом произвести отбор является применение расширенного автофильтра. Рассмотрим, как это сделать на конкретном примере.
-
Выделяем область на листе, среди данных которой нужно произвести выборку. Во вкладке «Главная» щелкаем по кнопке «Сортировка и фильтр». Она размещается в блоке настроек «Редактирование». В открывшемся после этого списка выполняем щелчок по кнопке «Фильтр».
- равно;
- не равно;
- больше;
- больше или равно;
- меньше.
При использовании любого из двух вышеуказанных методов фильтрация будет удалена, а результаты выборки – очищены. То есть, в таблице будет показан весь массив данных, которыми она располагает.
Способ 2: применение формулы массива
Сделать отбор можно также применив сложную формулу массива. В отличие от предыдущего варианта, данный метод предусматривает вывод результата в отдельную таблицу.
-
На том же листе создаем пустую таблицу с такими же наименованиями столбцов в шапке, что и у исходника.
Опять набираем сочетание клавиш Ctrl+Shift+Enter.
Теперь у нас имеется готовая выборка по указанному ограничению в отдельной надлежащим образом оформленной таблице.
Способ 3: выборка по нескольким условиям с помощью формулы
Так же, как и при использовании фильтра, с помощью формулы можно осуществлять выборку по нескольким условиям. Для примера возьмем всю ту же исходную таблицу, а также пустую таблицу, где будут выводиться результаты, с уже выполненным числовым и условным форматированием. Установим первым ограничением нижнюю границу отбора по выручке в 15000 рублей, а вторым условием верхнюю границу в 20000 рублей.
-
Вписываем в отдельном столбце граничные условия для выборки.
В последующие колонки вписываем точно такие же формулы, только изменив координаты сразу после наименования оператора ИНДЕКС на соответствующие нужным нам столбцам, по аналогии с предыдущим способом.
Способ 4: случайная выборка
В Экселе с помощью специальной формулы СЛЧИС можно также применять случайный отбор. Его требуется производить в некоторых случаях при работе с большим объемом данных, когда нужно представить общую картину без комплексного анализа всех данных массива.
-
Слева от таблицы пропускаем один столбец. В ячейке следующего столбца, которая находится напротив первой ячейки с данными таблицы, вписываем формулу:
Как видим, выборку в таблице Excel можно произвести, как с помощью автофильтра, так и применив специальные формулы. В первом случае результат будет выводиться в исходную таблицу, а во втором – в отдельную область. Имеется возможность производить отбор, как по одному условию, так и по нескольким. Кроме того, можно осуществлять случайную выборку, использовав функцию СЛЧИС.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
Еще статьи по данной теме:
Все это конечно здорово.
Фильтр ставится и по другому быть не может.
Однако не понятно как работать с этими данными.
Допустим я дошел до пункта 6. и у меня набор выручек подходящих по условиям. теперь мне нужно сосчитать какова сумма выручек ЭТОЙ ВЫБОРКИ, какая среднедневная выручка ЭТОЙ ВЫБОРКИ и каков средний вес ЭТОЙ ВЫБОРКИ в общей сумме выручек.
При попытке высчитать сумму выручек ЭТОЙ ВЫБОРКИ — стандартными средствами( формула сумм)- получаем просто сумму ВСЕХ выручек.
о следующих шагах говорить вообще не имеет смысла тогда.
Спасибо очень занимательно, а то я вечно забываю как ставить фильтр в таблицах)
Читайте также: