» Excel FAQ (часть 5)

denisAzef
Зайдите на сайт www.youtube.com и наберите в строке поиска "консолидация excel"
В найденных видеороликах достаточно подробно разбираются задачи, похожие на Ваши.

друзья, я так и делал.
странно, что с видео, но переделал
http://vimple.ru/84334b1c9e85416f8c9070a3979a979f Надеюсь это внесет ясность.

VictorKos пасибо ... только думаю консолидация тут не пойдет, еще одно условие я упустил ... во вкладке 4 у меня уже стоят №, и их больше чем во вкладке 1, 2,3, и по ним у меня нет никаких данных,а
Необходимо получить такую таблицу
вкладка 4
№ объем
1 300
80 0
2 330
3 15
12 210
11 100
77 0
10 160
18 30
9 40
64 0
тоесть мне необходимо чтобы сумма номеров в 1,2,3 вкладкх распределилась в четверной по соответствующим номерам

Futurism
IE только кажет видео. Chrom нет. зачем ты меняешь формулы, они и так уже прописаны, также у тебя настроены формулы с использованием относительных ссылок, у меня абсолютные.

Все номера куда ты протягиваешь формулы отсутствуют в книге симок. для проверки работоспособности, возьми самый первый номер и отчета и вставь его в книгу с симками, вместо другого номера. в отчете должны подтянуться с разу данные того чувака у которого ты сменил номер.

в общем тебе дали пищу для размышления, теперь давай сам. иначе ты не научишься.

Futurism ты только вопросы ставишь, сам ничего анализировать не пытаешься.
На видео видно 12 номеров: первые 4 отформатированы влево и в левом верхнем углу стоит зелёный треугольник. Это означает, что в этих ячейках число сохранено как текст. Если нажать на зелёный треугольник, откроется меню, где будет сказано, что число сохранено как текст и будет предложено преобразовать в число. Далее идут 3 номера, отформатированные вправо, это означает, что они сохранены как число. У этих номеров определены ФИО и должность. Затем идут ещё 3 номера, отформатированные влево, которые не определены. А затем ещё 2 номера, отформатированные вправо (т.е. числа), у которых определены ФИО и должность.

Разбираться нужно с каждым конкретным номером, почему он не найден? Чем отличаются те, номера, которые найдены, от тех, которые не найдены. Проблема не в формуле, а в том, какие данные она анализирует. С этим нужно разбираться.

Если ты используешь стиль ссылок R1C1, то в формулах не может быть никаких $B:$G. В формулах могут быть только R(строки) и C(колонки). Ячейка A11— это R11C1, диапазон $B:$G— это C2:C7. Сначала формулы приведи к виду R1C1 либо отключи стиль ссылок R1C1 (сервис->параметры->общие)

Для того чтобы не разбиратся с форматированием ячеек (текст/цифры) содержащих чистые числовые данные достаточно в формуле добавить простую операцию типа =A1+0 , добавление нуля не меняет значение но преобразовывает формат в цифровой.

LaCastet
отформатированные номера это вставленные руками из книги симок, для проверки работоспособности формул. формат числа (текст) на работу не влияет. просто в отчете нет ни одного телефона который есть в книге симок, чтобы он вывел нужную инфу

KF121
LaCastet
vikkiv
Спасибо вам друзья, с вашей помощью я добил это, я понял как верно делать. Все в порядке теперь. Как вы все говорили, важно данные привести к единому формату телефонов и форматированию ячеек.

а можно номера телефонов в отчете( файл х100128966449_20130930.xlsx), где стоят зеленые пометки не по одному преобразовывать в число, а сразу выделить и преобразовать. Они там идут начиная с А11

Futurism
Число/текст не важно. формулы будут работать. главное чтобы номера 100% совпадало. а изменить формат просто. выделяешь все ячейки и на главной вкладке из выпадающего списка выбираешь формат http://clip2net.com/s/5WYmjj

KF121
спасибо огромное))

По моему вопросу ктоьнить может помоч.. как консолидировать данные если есть (пустые ячейки) в том файле в который нужно собрать данные

denisAzef
Я бы взял не сумма если, а СУММПРОИЗВ в таком формате

Код: СУММПРОИЗВ ((первое условие)*(второе условие)*(...)*(диапазон суммирования))
Есть вкладки A,B,C,D, на каждой из них в десяти строках ваши данные:
На вкладке D в первом столбце числа в столбик начиная с единицы
A1 = 1, A2=2, A3=3, ... A10=10
B1 =
СУММПРОИЗВ((A!$A$1:$A$10=A1)*(A!$B$1:$B$10))+
СУММПРОИЗВ((B!$A$1:$A$10=A1)*(B!$B$1:$B$10))+
СУММПРОИЗВ((C!$A$1:$A$10=A1)*(C!$B$1:$B$10))

B2 =
СУММПРОИЗВ((A!$A$1:$A$10=A2)*(A!$B$1:$B$10))+
СУММПРОИЗВ((B!$A$1:$A$10=A2)*(B!$B$1:$B$10))+
СУММПРОИЗВ((C!$A$1:$A$10=A2)*(C!$B$1:$B$10))

...

B10 =
СУММПРОИЗВ((A!$A$1:$A$10=A10)*(A!$B$1:$B$10))+
СУММПРОИЗВ((B!$A$1:$A$10=A10)*(B!$B$1:$B$10))+
СУММПРОИЗВ((C!$A$1:$A$10=A10)*(C!$B$1:$B$10))

SFC ... попробовал работает походу, щас тестить буду, отпишусь работает или нет.

Друзья, кто знает почему когда отправляешь эксель с каталогом, получатели когда его открывают видят все в кракозябрах. Хотя у нас нормально все отображается. Это у них траблы или у нас?

Futurism возможно вы используете шрифт, который не установлен на компьютере получателей.

кстати да, возможно, а там можно как - то кодировку изменить. И почему к ним эксель пришел в формате... .ТХТ 0_0

Futurism каким способом вы его посылали: вставляли в письмо, выкладывали на файлообменник, непосредственно файл или помещали в архив?

LaCastet
через 1С напрямую. там можно прикреплять фаил.

Futurism по подробнее, какая версия 1С, куда там можно прикреплять файл?

LaCastet
1с 8.2, но конфигурация не типовая. Оперативный учет. там просто выбираете контрагента, нажимаете плюс и добавить файлик кот. надо отправить. У меня есть версия, что этот файлик весит 10 метров, можно ли как-то ужимать файл экселя, но без архивирования. Сам фаил представляет собой такую картинку

Futurism
1) Если файл xlsx то он уже 'ужат', переименуй в .zip и открой - сразу всё ясно будет
2) Картинки сами по себе и так сжаты (jpeg, jpg, png, gif, кроме bmp/tiff, в общем зависит от форматов, не тема для этой ветки) .. большего сжатия там не особо получится (~5%), разве что изменить сами изображения перед вставкой (т.е. не меняя их размер масштабами, а пересжать в другое разрешение сохранив пропорции и только потом вставить).

vikkiv ясно. Ну да ладно бог с ним с файлом.

не понимаю почему формулы не вставляются.
мне надо этот столбик 82-ой

скопировать в 9 столбец

но при копировании формулы не проставляются.

что тут не так то?))

Futurism
1) потому что ты используешь относительные ссылки а не абсолютные (RC[...])
2) Данные ячейки у тебя объедены
3) зачем вставлять формулы, если можно просто Ctrl + C и потом Ctrl + V

Ctrl + C и потом Ctrl + V дают такой же результат , вот я и подумал может параметы вставки надо изменить . А как мне их сделать абсолютными?

Futurism

Цитата:

А как мне их сделать абсолютными?

Кликни мышкой в строке формул и нажимай F4 пока не получишь такой вид:
=МАКС(R5C22:R5C55)/R5C8

LaCastet
сработало, спасибо.

LaCastet
зачем так сложно.

убрать галочку в настройках
http://SSMaker.ru/3fe23965/

KF121
Так это стиль ссылок поменяется, а не относительные ссылки на абсолютные.

друзья , продолжая мучить этот файлик.
эти цифры

на самом деле счетчики бумаги на принтерах
они делаются по месяцам. Например сентябрь, у принтера такого то в месяц расход 552 листа, а в день 24. Желтым цветом помечено сколько вообще принтер за свою нелегкую жизнь напечатал
например в августе было 608296 , прибавим сентябрьские 552 получим 608848. В октябре будет уже другое число.
Можно ли сделать ,что ,когда добавляешь новый столбец для нового месяца автоматические проставлялась формула , ну, например для октября
R5C80 (т.е. нынешнее итоговое количество) + R6C80)(новое значение за новый месяц, т.е. за этот октябрь)
итак для всех принтеров ,которые есть по всем последующим месяцам.