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

Вход

Регистрация

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

 

= Мир MS Excel/Преобразование данных в таблице xls с одной графой в таблицу - Мир MS Excel

Старая форма входа
  • Страница 1 из 1
  • 1
Модератор форума: китин, _Boroda_, DrMini  
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Преобразование данных в таблице xls с одной графой в таблицу (Формулы/Formulas)
Преобразование данных в таблице xls с одной графой в таблицу
Листва Дата: Среда, 28.10.2015, 23:07 | Сообщение № 1
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Добрый день.
Подскажите как решить проблему преобразования данных. Никак не могу понять с помощью формул вообще возможно преобразовать указанные данные или нет. С макросами не дружу, поэтому вариант с ними не рассматривала.

Исходная форма данных (все в одну графу):

Заказчик: Иванов И. И. 28.10.2015, 12:53

Цемент 50 кг 100 руб 3000 кг

Бетон М200 2500 руб 32 м3

Заказчик: Петров С. И. 28.10.2015, 13:07

Цемент 50 кг 100 руб 1200 кг
Бетон М200 2500 руб 6 м3
Песок строительный 200 руб. 16 тн

Требуемая форма данных (3 графы):
Иванов И. И. Цемент 50 кг 100 руб 3000 кг 3000
Иванов И. И. Бетон М200 2500 руб 32 м3 32
Петров С. И. Цемент 50 кг 100 руб 1200 кг 1200
Петров С. И. Бетон М200 2500 руб 6 м3 6
Петров С. И. Песок строительный 200 руб. 16 тн 16

Файл с примером во вложении.

Буду признательна за подсказку.
К сообщению приложен файл: __.xlsx (39.7 Kb)


Сообщение отредактировал Листва - Среда, 28.10.2015, 23:08
 
Ответить
СообщениеДобрый день.
Подскажите как решить проблему преобразования данных. Никак не могу понять с помощью формул вообще возможно преобразовать указанные данные или нет. С макросами не дружу, поэтому вариант с ними не рассматривала.

Исходная форма данных (все в одну графу):

Заказчик: Иванов И. И. 28.10.2015, 12:53

Цемент 50 кг 100 руб 3000 кг

Бетон М200 2500 руб 32 м3

Заказчик: Петров С. И. 28.10.2015, 13:07

Цемент 50 кг 100 руб 1200 кг
Бетон М200 2500 руб 6 м3
Песок строительный 200 руб. 16 тн

Требуемая форма данных (3 графы):
Иванов И. И. Цемент 50 кг 100 руб 3000 кг 3000
Иванов И. И. Бетон М200 2500 руб 32 м3 32
Петров С. И. Цемент 50 кг 100 руб 1200 кг 1200
Петров С. И. Бетон М200 2500 руб 6 м3 6
Петров С. И. Песок строительный 200 руб. 16 тн 16

Файл с примером во вложении.

Буду признательна за подсказку.
Автор - Листва
Дата добавления - 28.10.2015 в 23:07
_Boroda_ Дата: Среда, 28.10.2015, 23:53 | Сообщение № 2
Группа: Админы
Ранг: Местный житель
Сообщений: 16912
Репутация: 6616 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Так нужно?
Код
=ЕСЛИОШИБКА(ПСТР(ИНДЕКС(ПРОСМОТР(СТРОКА($2:$12);СТРОКА($2:$12)/(ЛЕВБ(A$2:A$12;3)="Зак");A$2:A$12);НАИМЕНЬШИЙ(ЕСЛИ((ЛЕВБ(A$2:A$12;3)<>"Зак")*(A$2:A$12<>"");СТРОКА(A$1:A$11));СТРОКА(F1)));11;ПОИСК(".";ИНДЕКС(ПРОСМОТР(СТРОКА($2:$12);СТРОКА($2:$12)/(ЛЕВБ(A$2:A$12;3)="Зак");A$2:A$12);НАИМЕНЬШИЙ(ЕСЛИ((ЛЕВБ(A$2:A$12;3)<>"Зак")*(A$2:A$12<>"");СТРОКА(A$1:A$11));СТРОКА(F1))))-8);"")

Код
=ЕСЛИОШИБКА(ИНДЕКС(A$1:A$12;НАИМЕНЬШИЙ(ЕСЛИ((ЛЕВБ(A$2:A$12;3)<>"Зак")*(A$2:A$12<>"");СТРОКА(A$2:A$12));СТРОКА(F1)));"")

Код
=ЕСЛИ(F19="";"";--СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(" "&F19;" ";ПОВТОР(" ";999));999*(ДЛСТР(F19)-ДЛСТР(ПОДСТАВИТЬ(F19;" ";)));999)))

Первые две формулы - формулы массива. Вводятся одновременным нажатием Контрл Шифт Ентер.
[p.s.]Формулы (особенно первую) не оптимизировал - некогда, написал, что первое в голову пришло.
К сообщению приложен файл: 646464_1.xlsx (10.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеТак нужно?
Код
=ЕСЛИОШИБКА(ПСТР(ИНДЕКС(ПРОСМОТР(СТРОКА($2:$12);СТРОКА($2:$12)/(ЛЕВБ(A$2:A$12;3)="Зак");A$2:A$12);НАИМЕНЬШИЙ(ЕСЛИ((ЛЕВБ(A$2:A$12;3)<>"Зак")*(A$2:A$12<>"");СТРОКА(A$1:A$11));СТРОКА(F1)));11;ПОИСК(".";ИНДЕКС(ПРОСМОТР(СТРОКА($2:$12);СТРОКА($2:$12)/(ЛЕВБ(A$2:A$12;3)="Зак");A$2:A$12);НАИМЕНЬШИЙ(ЕСЛИ((ЛЕВБ(A$2:A$12;3)<>"Зак")*(A$2:A$12<>"");СТРОКА(A$1:A$11));СТРОКА(F1))))-8);"")

Код
=ЕСЛИОШИБКА(ИНДЕКС(A$1:A$12;НАИМЕНЬШИЙ(ЕСЛИ((ЛЕВБ(A$2:A$12;3)<>"Зак")*(A$2:A$12<>"");СТРОКА(A$2:A$12));СТРОКА(F1)));"")

Код
=ЕСЛИ(F19="";"";--СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(" "&F19;" ";ПОВТОР(" ";999));999*(ДЛСТР(F19)-ДЛСТР(ПОДСТАВИТЬ(F19;" ";)));999)))

Первые две формулы - формулы массива. Вводятся одновременным нажатием Контрл Шифт Ентер.
[p.s.]Формулы (особенно первую) не оптимизировал - некогда, написал, что первое в голову пришло.
Автор - _Boroda_
Дата добавления - 28.10.2015 в 23:53
Листва Дата: Четверг, 29.10.2015, 10:05 | Сообщение № 3
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, спасибо Вам огромное.
Пойду попробую "пережевать" прочитанное и наложить на свой полный исходник.
Я слишком хорошо о себе думала, надеясь, что главное понять какими функциями открывается моя задача... :o
 
Ответить
Сообщение_Boroda_, спасибо Вам огромное.
Пойду попробую "пережевать" прочитанное и наложить на свой полный исходник.
Я слишком хорошо о себе думала, надеясь, что главное понять какими функциями открывается моя задача... :o
Автор - Листва
Дата добавления - 29.10.2015 в 10:05
Листва Дата: Четверг, 29.10.2015, 10:32 | Сообщение № 4
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, можно несколько вопросов по формуле?
- мой эксель "бьет" те же формулы, какая версия нужна, чтобы можно было применить ваши формулы для получения корректного расчета?
- не пойму каково назначение ячейки F1, фигурирующей в формуле...; вы ее какой ролью наделили?

П. С. поняла почему бьет - на работе стоит экс.2003, попробую совместить с вашим решением...


Сообщение отредактировал Листва - Четверг, 29.10.2015, 10:39
 
Ответить
Сообщение_Boroda_, можно несколько вопросов по формуле?
- мой эксель "бьет" те же формулы, какая версия нужна, чтобы можно было применить ваши формулы для получения корректного расчета?
- не пойму каково назначение ячейки F1, фигурирующей в формуле...; вы ее какой ролью наделили?

П. С. поняла почему бьет - на работе стоит экс.2003, попробую совместить с вашим решением...
Автор - Листва
Дата добавления - 29.10.2015 в 10:32
Russel Дата: Четверг, 29.10.2015, 11:19 | Сообщение № 5
Группа: Друзья
Ранг: Старожил
Сообщений: 1394
Репутация: 320 ±
Замечаний: 0% ±

Excel 2010
С более простыми для понимания формулами, получившуюся таблицу необходимо отфильтровать.
К сообщению приложен файл: -1-.xlsx (11.5 Kb)

QIWI 9173973973
 
Ответить
СообщениеС более простыми для понимания формулами, получившуюся таблицу необходимо отфильтровать.
Автор - Russel
Дата добавления - 29.10.2015 в 11:19
Листва Дата: Четверг, 29.10.2015, 11:56 | Сообщение № 6
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Russel, спасибки!
Действительно чуть проще, в связи с чем кое-что поддалось моему рассудку.
Пойду обкатывать на полном массиве.
 
Ответить
СообщениеRussel, спасибки!
Действительно чуть проще, в связи с чем кое-что поддалось моему рассудку.
Пойду обкатывать на полном массиве.
Автор - Листва
Дата добавления - 29.10.2015 в 11:56
Листва Дата: Четверг, 29.10.2015, 14:26 | Сообщение № 7
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Russel, ФИО и наименование материала красиво легло на мой массив, с ними никаких вопросов, только благодарность за подсказку)
А вот кол-во с IFERROR на моем экс.2003 не встает как надо хоть я и меняю на ЕСЛИОШИБКА. И что-то суть формулы с кол-вами никак...(((
 
Ответить
СообщениеRussel, ФИО и наименование материала красиво легло на мой массив, с ними никаких вопросов, только благодарность за подсказку)
А вот кол-во с IFERROR на моем экс.2003 не встает как надо хоть я и меняю на ЕСЛИОШИБКА. И что-то суть формулы с кол-вами никак...(((
Автор - Листва
Дата добавления - 29.10.2015 в 14:26
SLAVICK Дата: Четверг, 29.10.2015, 14:39 | Сообщение № 8
Группа: Модераторы
Ранг: Старожил
Сообщений: 2290
Репутация: 766 ±
Замечаний: 0% ±

2019
В 2003-м еще не было этой функции.
Попробуйте:
Код
=ЕСЛИ(
ЕОШИБКА(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(C30;" ";ПОВТОР(" ";100));(ОКРУГЛВНИЗ(ДЛСТР(ПОДСТАВИТЬ(C30;" ";ПОВТОР(" ";100)))/100;0)-1)*100;100)));
"";
СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(C30;" ";ПОВТОР(" ";100));(ОКРУГЛВНИЗ(ДЛСТР(ПОДСТАВИТЬ(C30;" ";ПОВТОР(" ";100)))/100;0)-1)*100;100)))
К сообщению приложен файл: 8771584.xlsx (11.3 Kb)

Иногда все проще чем кажется с первого взгляда.
 
Ответить
СообщениеВ 2003-м еще не было этой функции.
Попробуйте:
Код
=ЕСЛИ(
ЕОШИБКА(СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(C30;" ";ПОВТОР(" ";100));(ОКРУГЛВНИЗ(ДЛСТР(ПОДСТАВИТЬ(C30;" ";ПОВТОР(" ";100)))/100;0)-1)*100;100)));
"";
СЖПРОБЕЛЫ(ПСТР(ПОДСТАВИТЬ(C30;" ";ПОВТОР(" ";100));(ОКРУГЛВНИЗ(ДЛСТР(ПОДСТАВИТЬ(C30;" ";ПОВТОР(" ";100)))/100;0)-1)*100;100)))
Автор - SLAVICK
Дата добавления - 29.10.2015 в 14:39
_Boroda_ Дата: Четверг, 29.10.2015, 15:08 | Сообщение № 9
Группа: Админы
Ранг: Местный житель
Сообщений: 16912
Репутация: 6616 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Алина, тот же файл с теми же формулами, только без ЕСЛИОШИБКА. Ошибки скрыты условным форматированием.
У меня в 2003 вроде работает.

Цитата Листва, 29.10.2015 в 10:32, в сообщении № 4
не пойму каково назначение ячейки F1

Это не ячейка F1, это функция СТРОКА(F1) - дает нам единицу (номер строки; можно было сделать ссылку на любой столбец, главное, что на первую строку). При протяжке формулы вниз ссылка меняется - F2, F3, ... и дает уже не 1, а 2, 3, ... А все это у нас внутри функции НАИМЕНЬШИЙ в качестве второго аргумента (почитайте справку по этой функции)
К сообщению приложен файл: 33665522_1.xls (33.5 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеАлина, тот же файл с теми же формулами, только без ЕСЛИОШИБКА. Ошибки скрыты условным форматированием.
У меня в 2003 вроде работает.

Цитата Листва, 29.10.2015 в 10:32, в сообщении № 4
не пойму каково назначение ячейки F1

Это не ячейка F1, это функция СТРОКА(F1) - дает нам единицу (номер строки; можно было сделать ссылку на любой столбец, главное, что на первую строку). При протяжке формулы вниз ссылка меняется - F2, F3, ... и дает уже не 1, а 2, 3, ... А все это у нас внутри функции НАИМЕНЬШИЙ в качестве второго аргумента (почитайте справку по этой функции)
Автор - _Boroda_
Дата добавления - 29.10.2015 в 15:08
Листва Дата: Четверг, 29.10.2015, 21:21 | Сообщение № 10
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
SLAVICK, спасибо, буду вживлять.
Я правильно понимаю, что если у меня разные версии экселя (дом и работа), то надо писать формулу в наиболее старой, чтобы работала на обеих версиях?
 
Ответить
СообщениеSLAVICK, спасибо, буду вживлять.
Я правильно понимаю, что если у меня разные версии экселя (дом и работа), то надо писать формулу в наиболее старой, чтобы работала на обеих версиях?
Автор - Листва
Дата добавления - 29.10.2015 в 21:21
Листва Дата: Четверг, 29.10.2015, 21:28 | Сообщение № 11
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, млин, где-то я ступила значит… Я в экс.2003 пыталась наложить вашу формулу на свой массив и расчет выдавал#ИМЯ. Начала читать про функции и пришла к выводу, что экс2003 не поддерживает iferror, вот и начала искать ему альтернативы. Наверное, в другом ошиблась. Поковыряюсь завтра на работе в 2003-м.

Про НАИМЕНЬШИЙ и СТРОКА: пошла повышать свой (как мне казалось ранее) базовый уровень знания экс.

Спасибо за ответы!
 
Ответить
Сообщение_Boroda_, млин, где-то я ступила значит… Я в экс.2003 пыталась наложить вашу формулу на свой массив и расчет выдавал#ИМЯ. Начала читать про функции и пришла к выводу, что экс2003 не поддерживает iferror, вот и начала искать ему альтернативы. Наверное, в другом ошиблась. Поковыряюсь завтра на работе в 2003-м.

Про НАИМЕНЬШИЙ и СТРОКА: пошла повышать свой (как мне казалось ранее) базовый уровень знания экс.

Спасибо за ответы!
Автор - Листва
Дата добавления - 29.10.2015 в 21:28
Листва Дата: Четверг, 29.10.2015, 21:41 | Сообщение № 12
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
_Boroda_, да(( и пытаюсь тупо положить на свой массив.
Надеюсь, что когда изучу некоторые впервые увиденные функции - у меня получится "не тупо", а хотя бы понять, если уж не написать.
 
Ответить
Сообщение_Boroda_, да(( и пытаюсь тупо положить на свой массив.
Надеюсь, что когда изучу некоторые впервые увиденные функции - у меня получится "не тупо", а хотя бы понять, если уж не написать.
Автор - Листва
Дата добавления - 29.10.2015 в 21:41
Листва Дата: Четверг, 29.10.2015, 22:23 | Сообщение № 13
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Поможете подшаманить ошибку…? Сбоит у меня...
К сообщению приложен файл: __.xls (35.5 Kb)
 
Ответить
СообщениеПоможете подшаманить ошибку…? Сбоит у меня...
Автор - Листва
Дата добавления - 29.10.2015 в 22:23
Pelena Дата: Четверг, 29.10.2015, 22:39 | Сообщение № 14
Группа: Админы
Ранг: Местный житель
Сообщений: 19521
Репутация: 4634 ±
Замечаний: ±

Excel 365 & Mac Excel
Так?
И формулу массива надо вводить сочетанием клавиш Ctrl+Shift+Enter
К сообщению приложен файл: -23-.xls (44.5 Kb)

"Черт возьми, Холмс! Но как??!!"
Ю-money 41001765434816
 
Ответить
СообщениеТак?
И формулу массива надо вводить сочетанием клавиш Ctrl+Shift+Enter
Автор - Pelena
Дата добавления - 29.10.2015 в 22:39
_Boroda_ Дата: Четверг, 29.10.2015, 23:33 | Сообщение № 15
Группа: Админы
Ранг: Местный житель
Сообщений: 16912
Репутация: 6616 ±
Замечаний: ±

2003; 2007; 2010; 2013 RUS
Посмотрите, может, так понятнее будет. Там примечание еще.
А про формулы массива не заметили? я писал в своем первом посте
Цитата _Boroda_, 28.10.2015 в 23:53, в сообщении № 2
Первые две формулы - формулы массива. Вводятся одновременным нажатием Контрл Шифт Ентер.
К сообщению приложен файл: _1.xls (47.0 Kb)

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995
 
Ответить
СообщениеПосмотрите, может, так понятнее будет. Там примечание еще.
А про формулы массива не заметили? я писал в своем первом посте
Цитата _Boroda_, 28.10.2015 в 23:53, в сообщении № 2
Первые две формулы - формулы массива. Вводятся одновременным нажатием Контрл Шифт Ентер.
Автор - _Boroda_
Дата добавления - 29.10.2015 в 23:33
Листва Дата: Понедельник, 02.11.2015, 23:18 | Сообщение № 16
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Pelena, _Boroda_, спасибо!
Выпала немного из изучаемого вопроса…
Сейчас буду доворачивать.


Сообщение отредактировал Листва - Вторник, 03.11.2015, 00:13
 
Ответить
СообщениеPelena, _Boroda_, спасибо!
Выпала немного из изучаемого вопроса…
Сейчас буду доворачивать.
Автор - Листва
Дата добавления - 02.11.2015 в 23:18
Листва Дата: Четверг, 05.11.2015, 00:08 | Сообщение № 17
Группа: Пользователи
Ранг: Новичок
Сообщений: 13
Репутация: 0 ±
Замечаний: 0% ±

Excel 2010
Спасибо за подсказки, в несколько этапов, но делаю, что требуется…

Еще по данной таблице вопрос: как теперь просуммировать данные в разрезе сотрудников?
Вариант с выносом уникального списка в отдельный столбец и формулой СУММЕСЛИ мне понятен, он во вложении.
А без выноса уникального списка (и без макросов) можно? Или только через сводную таблицу?
[moder]Это другой вопрос. Отдельную тему создавайте.
К сообщению приложен файл: __04.11.15.xlsx (32.2 Kb)


Сообщение отредактировал _Boroda_ - Четверг, 05.11.2015, 01:24
 
Ответить
СообщениеСпасибо за подсказки, в несколько этапов, но делаю, что требуется…

Еще по данной таблице вопрос: как теперь просуммировать данные в разрезе сотрудников?
Вариант с выносом уникального списка в отдельный столбец и формулой СУММЕСЛИ мне понятен, он во вложении.
А без выноса уникального списка (и без макросов) можно? Или только через сводную таблицу?
[moder]Это другой вопрос. Отдельную тему создавайте.
Автор - Листва
Дата добавления - 05.11.2015 в 00:08
Мир MS Excel » Вопросы и решения » Вопросы по Excel » Преобразование данных в таблице xls с одной графой в таблицу (Формулы/Formulas)
  • Страница 1 из 1
  • 1
Поиск:

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