» Excel FAQ (часть 3)

Добрый день!
Помогите пожалуйста...
Ситуация №1                            
1. Есть книга (определенное изделие), в которой более 10000 позиций и около 30-ти цехов изготовителей.
2. Книга (изделие) обновляется где-то 1-2 раза в месяц.        
3. При обновлении аннулируется где-то 100-300 позиций и одновременно вводятся вновь порядка 100-300 других, отличных от аннулированных. Кол-во аннулированных с кол-вом введенных как правило не совпадает.
Вопрос:
1. Как сделать так, чтобы не вручную, а как нибудь автоматически новый список сверялся со старым (по "обозначению" т.е. столбцу А). И вновь введенные позиции отмечались каким-нибудь значением (наличия или отсутствия) в специально-заданном (в конце таблицы) пустом столбце (AN)?
2. Старый список сверялся с новым и аннулированные позиции тоже как-нибудь отмечались (в АО)?

Пример

Ситуация №2
1. Периодически присылается новая база данных (книга(другое изделие)) - без числовых значений в ячейках цехов.
2. В новой книге(изделии) процентов 80 новые детали и 20% совпадающих с другим изделием. Как правило эти 20% - это стандартные детали употребляющиеся везде.
Вопрос:                                
1. Каким образом из другой книги перенести значения этих 20% деталей в новую? Т.е. по каждому "обозначению" должен производиться поиск-сравнение и в дальнейшем каждому цеху должно проставляться ему присвоенное соответствующее значение (в листе "новое изделие). Я понимаю, что это делается с помощью ВПР, но как именно не могу сообразить.        

И там и там нужен впр

пример 1

- поиск новых позиций в новой таблице, формула пишется в новой таблице
ЕСЛИ( ЕОШИБКА( ВПР(имя изделия_из_новой_таблицы;лист_с_старыми_позициями!а1:а10000;1;ложь) ) ; "Новая позиция";"старая позиция")
впр ищет имя из новой таблицы, если находит то возвращает ее имя, если не находит то возвращает н/д. ЕОШИБКА в случае возврава ВПРом н/д выдаст ИСТИНА. ЕСЛИ в случае ИСТИНы со стороны ЕОШИБКА выдаст "новая позиция" в противном случае "старая позиция.
- Поиск старых позиций в старой таблице, формула пишется в старой таблице
ЕСЛИ( ЕОШИБКА( ВПР(имя изделия_из_старой_таблицы; лист_с_новыми_позициями!а1:а10000;1;ложь) ) ; "старая позиция";"Новая позиция")
вормула с точностью наоборот ))
на столбцы с формулами накладывем фильтр, и можем вбирать новые или старые позиции

пример 2

Поиск новых позиций по анологии с приведеным выше.


Вставка цеховых значений - тот же ВПР только вместо диапазона А1:А10000 вкладываем А1:АM10000 а номер столбца для возврата значения необходими задать предварительно в виде самой верхней строчки пронумеровоной 1,2,3,4и.т.д. но значения в столбцах должны совпадать, иначе прийдется еще и ГПР вписывать ))

ЕСЛИ( ЕОШИБКА( ВПР(имя изделия_из_новой_таблицы; лист_с_старыми_позициями!а1:AM10000;ссылка_на_первую_строку;ложь) ) ; ""; ВПР(имя изделия_из_новой_таблицы; лист_с_старыми_позициями!а1:AM10000;ссылка_на_первую_строку;ложь) )



диапазон поиска везде фиксируем $, а вот значения нужно зафиксировать либо построчно, либо столбцами...


мож лучше пример скинуть? или и так разберетесь?.. ибо перечитав понял что получилось запутано //

ZORRO2005

Цитата:

Можно сделать формат оси и получить:
http://slil.ru/25536883
Или сделать дополнительный столбец(Год/Месяц)
в исходных данных:

СПАСИБО ОГРОМНОЕ! Вы мне очень помогли.

Цитата:

Можно присвоить имя для диапазона, чтобы он стал динамическим
Код:=СМЕЩ($A$1;0;0;СЧЁТЗ($A$1:$A$10000);СЧЁТЗ($1:$1))

И за это особенно. У меня столько поименованных диапазонов, а я не додумался до такой простой вещи.

D2men
макросы в другой ветке обсуждают, ну думаю в твоём случае подойдёт простенький код типа

Sub macro()
Dim n As String
Dim x As Long
For x = 2 To 65536
n = Cells(x, 2).Value
Cells(x, 3).Activate
ActiveSheet.Pictures.Insert (n)
Next x
End Sub

где в столбце "B" стоят гиперрсылки на вставляемые картинки или текст с адресами, в столбце "A" например ФИО. Картины будут вставляцца в третий столбец. Вместо 65536 поставишь нижнюю границу диапазона

D2men
Пока пытаюсь разобраться что к чему и по ходу возник вопрос - а если сортировка по возрастанию отсутствует - это будет работать?
Да, и еще есть такой неприятный момент - а если в столбце А будут встречаться одинаковые значения, то это проблем не создаст?
Например: есть гайка диаметром 10 мм, она может навинчиваться на болт с длиной резьбы - 15 мм, 20 и т.д. (обратите внимание на листе "февраль" ячейки А11 и А12 входят соответственно в D11 и D12...). Проблемы будут ?

P.S. если не трудно, то покажите в примере.

lomaxx

Цитата:

Да, и еще есть такой неприятный момент - а если в столбце А будут встречаться одинаковые значения, то это проблем не создаст?

Создаст

Цитата:

она может навинчиваться на болт с длиной резьбы - 15 мм, 20 и т.д.

Эту разницу видно по столбцу D? Т.е по ячейкам D11 и D12?
Если Да, то придется делать уникальность по A&D и для ВПР сортировка не нужна будет.
Я думаю надо использовать СЧЁТЕСЛИ.

Чтобы нам было проще
Вы цветом выделите, что новое,что старое и впишите артикула, которые вы хотите получить на итоговой странице.

Подскажите плиз хоткей для полосы прокрутки на одну строку вниз.

По правой кнопке на полосе такое проходит. Во многих приложениях помогало КТРЛ+стрелка вниз, а тут при использовании такого сочетания происходит скачок на самую последнюю ячейку внизу или вверху документа.
Заранее спасибо!

Здравствуйте! Подскажите как решить проблему, или ткните носом. где это написано:
если в ячейке Екселя длинный кусок текста, то он отображается ввиде "###########". Соответственно и копируется из ячейки в буфер обмена также. Можно ли как-нибудь сделать так, чтобы текст оставался текстом независимо от размера? Поиск по "######" не ищет ))).

MOCKuT
Какая версия Excel? Пример выложить в какой-нибудь файловый обменник можешь? ###### это обычно на числовых значениях бывает. А какая длина этого текстового куска? Сколько в нем символов?

Mushroomer
Версия Excel 2003, но также было и в XP, про 2000 непомню. Пример тут: http://ifolder.ru/5663103
Формат столбца если даже специально ставлю Текстовый - всё-равно пофиг. Длину куска не считал, но где-то от 300 символов.

MOCKuT
Поставь формат Общий и (по желанию) переносить по словам.

Хм... o_O Спасибо! Чего-то сам не додумался....

Цитата:

[/q][q]где в столбце "B" стоят гиперрсылки на вставляемые картинки или текст с адресами

2 crotoff а как вместо прямого вставляния гиперссылки, переменую n создать сцепляя "F:\картинки" + имя файла которое содержится в соседней ячейке + расширение?

MOCKuT
Да я сам только экспериментальным путем дошел. Почему не работает обычный текстовый формат - непонятно

ZORRO2005

Цитата:

Чтобы нам было проще
Вы цветом выделите, что новое,что старое и впишите артикула, которые вы хотите получить на итоговой странице.

ПРИМЕР
А артикль - любой, какой удобнее...


Цитата:

Цитата:она может навинчиваться на болт с длиной резьбы - 15 мм, 20 и т.д.
Эту разницу видно по столбцу D? Т.е по ячейкам D11 и D12?

Да, так и есть...

D2men
предполагается что в столбце B находятся имена файлов-картинок с полными путями, соответствующие ФИО. Как я понял, сами имена файлов ты через ВПР выбираешь откуда-то из базы? Склеить имя файла с путём можно через оператор & или функцию Сцепить (), для одной ячейки пропишешь и растиражируешь дальше, конкретно это надо сам файл смотреть
пример http://slil.ru/25560871

lomaxx
Без дополнительного столбца не получилось: http://slil.ru/25561164
но если кто-нибудь решит эту ЗАДАЧУ то получится

2 crotoff как склеить в эксел я знаю, просто хотелось сделать это в теле макроса. Просто это будет некий каталог, постоянно меняющийся. и нехочется делать доп ячейку для урла, имя файла картинки есть всегда, а аодрес будет задан постоянный вот и нехотел делать ячейку с сылкой, ибо там и так многа инфы всякой...

ZORRO2005

Цитата:

DonkeyHottt
Посмотрите пример с дополнительным столбцом:
http://slil.ru/25535978
Столбец ведь можно спрятать.

Проверил на Вашем привере, все работает!
Стал применять к своей таблице, столкнулся с проблемой. Не могу правильно скопировать формулу массива. Простым перетягиванием - меняются номера строк, если добавить $ - неправильно считает. Как правильно размножить формулу {=ЕСЛИ(СЧЁТЕСЛИ(A2:A101;A2:A101)=0;"";1/СЧЁТЕСЛИ(A2:A101;A2:A101))} ?
Спасибо!

D2men
в теле макроса то же самое, переменной назначить склейку строковых данных - постоянной части и переменной, которая берётся из 1-го столбца с фамилиями, или выбирается из какой-то базы, или вычисляется по какому-то принципу.

Sub macro()
Dim n As String
Dim x As Long
For x = 2 To 65536
n = "c:\картинки\" & Cells(x, 1).Value & ".jpg"
Cells(x, 3).Activate
ActiveSheet.Pictures.Insert (n)
Next x
End Sub

DonkeyHottt
В верхнюю ячейку A2
пишете формулу:

Код: =ЕСЛИ(СЧЁТЕСЛИ(A2:A101;A2:A101)=0;"";1/СЧЁТЕСЛИ(A2:A101;A2:A101))

ZORRO2005
Что-то в этом есть, но что-то меня смущает. Посмотрю подробнее немного попозже.


Я получил советы по первой части вопроса, а вторая часть решаема?

ZORRO2005

Цитата:

В верхнюю ячейку A2
пишете формулу:

Код:=ЕСЛИ(СЧЁТЕСЛИ(A2:A101;A2:A101)=0;"";1/СЧЁТЕСЛИ(A2:A101;A2:A101))

затем выделяете A2:A101 и нажимаете CTRL+SHIFT+ENTER

А чтобы выделить диапазон на который распространяется эта формула
надо нажать CTRL+Ё или CTRL+? в любой клетке внутри диапазона

Что-то у меня ничего не получается. Формулу со своим диапазоном ну например R2:R500 я написал, нажал CTRL+SHIFT+ENTER чтобы превратить ее в формулу массива, но растянуть ее до 500 ячейки в таком виде не могу. меняются номера строк R3:R501 R4:R502 и т.д. В Вашем примере во всех ячейках столбца формула одинаковая. Нажимал Ctrl+Ё (Ё там где ~) Просто меняется форматирование ячеек. В ячейке где было значение формулы появляется сама формула. А как получить эту формулу во всех 500 ячейках так и не понял.

DonkeyHottt
Я же последовательность действий написал:
1. ......
2. затем выделяете A2:A101
а уже потом
3. CTRL+SHIFT+ENTER


Цитата:

Нажимал Ctrl+Ё (Ё там где ~)

Я бы тогда написал Ctrl+~
У меня Ё слева от правого SHIFT, там же и ?

lomaxx

Цитата:

Что-то

это что?

ZORRO2005

Цитата:

Я же последовательность действий написал:
1. ......
2. затем выделяете A2:A101
а уже потом
3. CTRL+SHIFT+ENTER

Там четыре блока A2:A101 (если результат этого действия просто получение скобок вокруг формулы то с этим все в порядке)

Цитата:

Я бы тогда написал Ctrl+~
У меня Ё слева от правого SHIFT, там же и ?

Нажимаю Ctrl+? - "Не найдено ни одной ячейки удовлетворяющей указанным условиям"
А что вобще должно произойти после нажатия Ctrl+?

Есть столбец в Excel (1....900), нужно каждое значение в столбце сложит с 6 . Как это сделать ?
Я сделал по формуле =B1:B900+6 , но по моему не правильно .

lomaxx
1-ая и вторая части:
http://slil.ru/25563999

Добавлено:
Misteri7

1 вариант:
Точно так же как в примере DonkeyHottt выше
т.е.
С1=B1:B900+6
затем выделяете С1:C900
и нажимаете CTRL+SHIFT+ENTER

2 вариант:
С1=B1:B900+6
нажимаете CTRL+SHIFT+ENTER
затем выделяете С1:C900
и нажимаете еще раз CTRL+SHIFT+ENTER

3 вариант:
C1=6
Нажимаете CTRL+C
Выделяете B1:B900
В меню Правка-Специальная вставка-сложить
OK

Misteri7
ZORRO2005
Мой вариант: в C1 формула =B1+6 , затем выделил C1:C900 и нажал Ctrl+D

2 ZORRO2005 VictorKos большое спасибо , все зделал 1 вариант

Ребят,в таблице,вместо букв А,В,С идут цифры... Как вернуть буковки на место???