Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Неправильная сумма в сводной таблице
 
#1
04.02.2012 22:15:50
Друзья, здравствуйте!  
 
Выручайте... Длительное время пользовался таким удобным инструментом Экселя как Сводная таблица, но вот недавно совершенно случайно обнаружил ошибку, которая повергла в шок!...  
 
Итак, по порядку.  
На работе зачастую требуется делать длинные спецификации оборудованя с многократно повторяющимися позициями, что собственно я неоднократно и делал. Потом с помощью Сводной таблицы генерил еще один лист и делал укрупненную спецификацию. В итоговом поле использовал суммирование. Раньше никаких ошибок не замечал.  
 
Теперь описываю свою последнюю спецификацию (см. вложение). Из перечня чужих файлов, предоставленных заказчиком, накопипастил в свой собственный файл позиции спецификации (лист "Данные"). Выделил все ячейки и сгенерил в новый лист "Сводная" Сводную таблицу. Особый интерес представляет поле "Итог", которое вычисляется суммированием поля "Количество ОБО" из листа "Данные".  
 
Вопрос вызыают все поля с нулевым значением: как такое могло получиться?  
Вот, например, смотрим строку №99 в листе "Сводная" - количество 0 шт. Для проверки идем в лист "Данные" и делаем поиск по артикулу "6419720" - находим сразу позицию (строка №297) "8 шт." Аналогичная ситуация с другими нулевыми итогами - они не должны быть нулевыми!  
Теперь еще один интересный факт: если, например, в ячейке №G297 вручную удалить "8" и снова вручную ввести "8", перейти в лист "Сводная" и обновить его правой клавишей мыши, то итог станет уже не нулевым, а равным "8". Почему так происходит? Меня терзают сомнения, что дело в формате данных столбцов "G" и "L" листа "Данные"...
 
 
 
#2
04.02.2012 22:19:44
Может быть там не число, а текст?
 
 
 
#3
04.02.2012 22:37:47
{quote}{login=Юрий М}{date=04.02.2012 10:19}{thema=}{post}Может быть там не число, а текст?{/post}{/quote}  
 
Ну так я перед этим данные столбцы целиком выделял и ставил формат "Числовой"... Или если ранее был текст, то таким образом в "Числовой" не перевести?  
 
Кому интересно может сам попробовать - удалить лист со сводной таблицей, еще раз назначить столбцам "G" и "L" формат "Числовой" и снова сгенерить Сводную таблицу и суммированием в итоговом поле по "Кол-во ОБО". Результат будет аналогичный.
 
 
 
#4
04.02.2012 22:40:54
Файл не смотрел - не специалист по сводным. Но, изменение формата ПОСЛЕ того, как данные уже есть в ячейке, не приведёт к преобразованию текста в число.
 
 
 
#5
04.02.2012 22:53:09
{quote}{login=Юрий М}{date=04.02.2012 10:40}{thema=}{post}Файл не смотрел - не специалист по сводным. Но, изменение формата ПОСЛЕ того, как данные уже есть в ячейке, не приведёт к преобразованию текста в число.{/post}{/quote}  
 
Т.е. это как?... Стоит в ячейке цифра, например, "8", в формате ячейки "Числовой, округление до...", а на самом деле это - текст?  
 
Сейчас ради интереса попробовал умножить в дополнительном столбце получить ячейку умножением из столбцов "L" или "G" на произвольное число. Везде все прекрасно умножилось без ошибки.
 
 
 
#6
04.02.2012 22:57:41
{quote}{login=MikeS}{date=04.02.2012 10:53}{thema=Re: }{post}{quote}{login=Юрий М}{date=04.02.2012 10:40}{thema=}{post}{/post}{/quote}Т.е. это как?{/post}{/quote}А так: в новом файле поставьте в ячейке формат "Текстовый", введите в ячейку пару единичек, Увидите, что это текст. Затем попробуйте поменять формат ячейки на "Числовой" - ничего не изменится.
 
 
 
#7
04.02.2012 23:04:13
решение - скопируйте все данные в столбце G:G и вставьте их туда же обратно, и выберите "преобразовать в число"
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
 
 
#8
04.02.2012 23:08:46
ps. после того как скопируете, выделите последнюю или первую ячейку с числом, нажмите Ctrl+A - и в правом верхнем углу нажмите "преобразовать в число"
Киса, я хочу Вас спросить, как художник — художника: Вы рисовать умеете?
 
 
 
#9
04.02.2012 23:22:49
Юрий, я кажется, начинаю догадываться про что Вы говорили...  
 
Скопировал значения из столбца "G" в соседний пустой и у меня на части ячеек сразу выскочило предупреждение о том, что "число сохранено как текст".  
 
Хорошо, допустим... Давайте обозначим проблему чуть шире. Предположим, что от заказчика к нам попала таблица с несколькими сотнями строк. Предположим, что в части строк цифры сохранены как текст. Вручную менять не вариант - их несколько сотен. Что делать? Как по простому перевести обратно в цифровой формат?  
 
P.S. Сейчас частично решил свою проблему из названия топика. Рассуждал так: суммирование у меня должно идти по столбцу "L". Ячейки в нем равны "G" или получены с помощью дополнительной формулы. В том случае, если "G" текстовый, то и "L" будет текстовый. Если в "L" - формула, то "L" - числовой. В соседнем пустом столбце я умножил "L" на единицу и суммирование в сводной таблице производил уже по этому значению. Все нулевые значения исчезли.  
И заметил еще одну особенность - сводная таблица мне сразу просуммировала, а не подсчитала количество повторений ("Количество").
 
 
 
#10
04.02.2012 23:28:09
{quote}{login=MikeS}{date=04.02.2012 11:22}{thema=Re: Re: Re: }{post}Скопировал значения из столбца "G" в соседний пустой и у меня на части ячеек сразу выскочило предупреждение о том, что "число сохранено как текст".{/post}{/quote}Вариант:    
1. В какой нибудь свободной ячейке пишем единичку (число).  
2. Копируем эту ячейку.  
3. Выделяем этот проблемный столбец.  
4. Правый клик - Специальная вставка - Умножить.
 
 
 
#11
04.02.2012 23:42:10
Гы-гы-гы... :)  
 
Залез в справку Экселя п.п. "Преобразование текстового формата в числовой".  
 
Цитата
 
1. В пустую ячейку введите цифру 1.    
2. Выделите эту ячейку и выберите в меню Правка команду Копировать.    
3. Выберите диапазон чисел, сохраненных как текст, которые требуется преобразовать.    
4. В меню Правка выберите команду Специальная вставка .    
5. В группе Операция выберите вариант умножить.    
6. Нажмите кнопку OK.    
7. Удалите содержимое ячейки, введенное на первом шаге.    
 
Спасибо всем откликнувшимся за помощь!!!
 
 
 
#12
05.02.2012 01:01:28
{quote}{login=MikeS}{date=04.02.2012 11:42}{thema=Re: }{post}  
Залез в справку Экселя п.п. "Преобразование текстового формата в числовой".  
{/post}{/quote}А почему до создания топика этого нельзя было сделать?
 
 
 
Страницы: 1
Читают тему
Наверх