» Excel FAQ (часть 3)

CEMEH

Цитата:

как пдставить, если ссылаемся на лист? (а если 100 листов?)

боюсь, что в данном случае может помочь только простенький макрос...
Выделаеешь нужные ячейки с формулой, делаешь Alt-F8 - "ПеренумероватьСсылкиНаЛисты" - выполнить - вуа-ля.. готово.
макрос нужен?

Добавлено:

vasiliy74
Цитата:

сорри не прав эта функция совсем не работает ни так ни так
а моя функция всё нармально находит
Например: =VLOOKUP($D1;Table;6;FALSE)

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

кто знает есть ли следующая функция:
значение в ячейке =123 а видно "привет"?? это для того чтобы фильтр работал при кросоте.

Добавлено:
SERGE_BLIZNUK

Цитата:

2) насколько я понимаю, задача то для Вас уже решена. т.е. вопрос можно считать закрытым?..

нет. просто не горит временное решение найдено, но это не правильно! есть есть функция то она должна быть использована SERGE_BLIZNUK

SERGE_BLIZNUK
Уж тогда лучше ткнуть в значок на панеле ))

Макрос нужен, только если можно, с коментариями. Уж больно хочется разобраться в его работе, а не тупо тыкать в кнопочки.

Кстати сказать о макросах я узнал дня три назад. Удивительная, надо сказать, штука. Я на работе за компом чудеса творю ))

vasiliy74

Цитата:

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

тогда делайте файлик, в котором Ваша "Таблица" и где поиск ВПР (он же VLOOKUP ) не работает, выкладываете его, сюда ссылочку и посмотрим, что Вы делаете не так...

CEMEH

Цитата:

Макрос нужен, только если можно, с коментариями. Уж больно хочется разобраться в его работе, а не тупо тыкать в кнопочки.

[more=Макрос на VBA перенумерации номеров книг]
Макрос переименования (перенумерации номеров книг в ссылках)

Код:
Sub ПеренумероватьСсылкиНаЛисты()
Dim Sformula, Snum As String
Dim FindString As String
Dim FirstBook, Pos1, i As Long
i = 0 ' счётчик книг (номеров)
Pos1 = Len("=Лист") + 1 ' просто для удобства
For Each c In Selection
If i = 0 Then
'номер, от которого нумеровать возьмём из 1-й ячейки выделения
Sformula = c.Formula
If (InStr(Sformula, "=Лист") <> 1) Or (InStr(Sformula, "!") = 0) Then
MsgBox "В первой ячейки выделения ссылка на другой лист не найдена." _
& Chr(13) & " Нечего делать!"
Exit Sub
End If
' выделим конкретно только номер книги (то, что после "Лист" и до "!"
Snum = Mid(Sformula, Pos1, InStr(Sformula, "!") - Pos1)
If Val(Snum) = 0 Then
MsgBox "Ошибка - книга без нумерации... Нечего делать, выходим."
Exit Sub
End If
FirstBook = Val(Snum) ' получить номер книги в 1-й выд.ячейке
i = 1 ' всё 1-й (при i=0 обработан, больше в эту ветку не заходим
Else
If (InStr(c.Formula, "=Лист") = 1) And (InStr(c.Formula, "!") > 1) Then
Snum = Mid(c.Formula, Pos1, InStr(c.Formula, "!") - Pos1)
If Val(Snum) > 0 Then
' Findstring это номер книги в текущей ячейке, который надо поменять
FindString = Mid(c.Formula, 2, InStr(c.Formula, "!") - 1)
' MsgBox "Меняем " & FindString & " на " & "Лист" & Trim(CStr(FirstBook + i)) & "!"
c.Formula = Replace(c.Formula, FindString, _
"Лист" & Trim(CStr(FirstBook + i)) & "!")
i = i + 1
End If
End If
End If

Next c
End Sub

SERGE_BLIZNUK

Цитата:

тогда делайте файлик, в котором Ваша "Таблица" и где поиск ВПР (он же VLOOKUP ) не работает, выкладываете его, сюда ссылочку и посмотрим, что Вы делаете не так...

Начал делать новый пример и всё получилось детально рассмотрел свой и понял что первый столбец неверно определяю спасибо!

SOS!!! спасите, помогите - Excel глючит ужасно!!!!

при попытке создать формулу (например, ставим знак "=") этот гад [excel] не позволяет спокойно сослаться на необходимую ячейку, а навязывает целый диапазон и, что самое неприятное - открывает все скрытые или сгруппированные столбцы и строчки.

Кто-нибудь догадывается, как это можно побороть?

Пожааааааааааааааааалуйста, помогите!! парюсь сильно!!!

Добавлено:
А что за сайт такой - Планета эксель?????
может быта там спрошу свой вопрос?...

Ребята, такая вот ситуация и незнаю как выйти... помогите разобратся:
есть форма в которую вноситься заказ, из нее при помощи функции "ВПР" количества переносятся в "козу" для составления инвойса, напротив артикулов, которые были заказаны стоят соответствующие количества, напротив тех артикулов, коих не заказывали соответственно стоят нули, а напротив названий групп стоит "Н\Д"....
далее приходиться в ручную удалять позиции напротив которых мы имеем 0 или же "Н\Д"...
суть вопроса: можно ли както сделать чтобы вручную не удалять строки с артикулом которые отображаються как 0 или "Н\Д".... тоесть это делалось автоматически! или хотябы чтобы выделялось цветом позиции которые подлежат удалению... автофильтр тут неприемлим.
Заранее спасибо!

Как создать свой автоформат? Что бы потом применять как типовые ????

Добавлено:
и как задать вид с плавающей точкой? например при формате ячейки общий запятая плавает, но разряды не разделяются(типа так: 1 000 000), а пишутся слитно(типа так: 1000000), как оставить плавающею запятаю (не фиксированное кол-во знаков после запятой) и разделить разряды??

vasiliy74
формат ячеек -> число -> (все форматы)
и там добавляй, эксперементируй

еще в хелпе поищи по строке "Коды числовых форматов"

И ещё вопрос может кто знает... как написать если дата больше 2003 то суммесли(... ?
+ к дате есть колонка с пизнаком фозможно обычной функцией?

Добавлено:

Цитата:

формат ячеек -> число -> (все форматы)

alt76
хе, это то я знаю толкько вот эксперементы успехом не увенчались непонимаю как плавающее кол-во знаков после запятой там определить

Добавлено:
вот он хелп : не чего нового...

Цитата:

Во встроенном числовом формате
В меню Формат выберите команду Ячейки, а затем откройте вкладку Число.

В списке Числовые форматы выберите Денежный, Финансовый, Процентный или Экспоненциальный.

В поле Число десятичных знаков введите требуемое число десятичных знаков.

Если требуется сменить знак денежных единиц, выберите требуемый знак в списке Обозначение.

а мне требуется переменное в зависемости от того сколько там знаков!

пример форматной строки:
[<=1]##0,00;##0,0
0,01 отображется как 0,01
1,01 отбражется как 1,0

Добавлено:
еще про хелп
ввожу поиск "Коды числовых форматов"
первая строчка: Коды числовых форматов
там:
....
Условия. Для определения числовых форматов, используемых только для чисел, отвечающих заданному условию, заключите данное условие в квадратные скобки. Оно должно состоять из оператора сравнения (Оператор сравнения. Знак, используемый в условиях для сравнения двух значений. Имеется шесть стандартных операторов: = (равно), > (больше), < (меньше), >= (больше или равно), <= (меньше или равно), <> (не равно).) и значения. Например, следующий формат отображает числа меньшие или равные 100 красным шрифтом, а числа больше 100 — синим.
[Красный][<=100];[Синий][>100]
...

так шо будь внимательнее

Zzvetlana
Цитата:

А что за сайт такой - Планета эксель?????

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

Nickolasha

Цитата:

чтобы выделялось цветом позиции которые подлежат удалению

это можно сделать через условное форматирование

а вот удаление только ручками, либо макросом.

Zzvetlana
Так Вы решили свою проблему? Если да - то в чём была причина?

нет (( не решила...
передёргала все настройки какие можно - глухо...
за ссылку спасибо - тот немногий вариант, который я сама не попробовала))))

alt76
понял спасибо

Добавлено:
UP не могу сравнить даты помогите плиз.. например:

A B C

vasiliy74

Цитата:

не могу сравнить даты

=ЕСЛИ(A1>ДАТА(2003;01;01);"БОЛЬШЕ";"меньше…")

alt76 помнишь

Цитата:

ДайПодстроку

слушай поставил я эту функцию и у меня Excel стал ресурсов есть намного больше с чем это может быть связанно?

Народ, а вто есть следующая проблема с Excel 2007:

Раскатали по офису его и первый глюк - не открываются файлы по двойному нажатию, исправил - пришлось отредактировать команду открытия в реестре для *.xls (теперь она вот так выглядит: "C:\Program Files\Microsoft Office\Office12\EXCEL.EXE" /e "%1"). Вроде стало открываться без тормозов.

НО! теперь другая проблема - ВСЕ файлы открываемые по двойному щелчку открываются в новом экземпляре Excel, что очень не удобно ;( Кто-нибудь знает как это можно исправить?

П.С. В ветке реестра HKEY_CLASSES_ROOT\Excel.Sheet.8\shell\Open\command сейчас лежит только ключ "по умолчанию" с вышеуказаным значением. остальные ключи и подразделы - удалены!

Вопрос:

В ячейке Excel можно организовать выпадающий список (и возможность выбора из списка нужного значения) но мне нужно реализовать немножко другую задачу:

Я начинаю набирать в ячейке фамилия_имя_отчество а Excel подставляет возможные значения. Что то подобное происходит при наборе имени адресата в Outlook. Если фамилия меня устраивает (Я набрал всего две-три буквы) я жму entr и в ячейку подставилась фамилия_имя_отчество целиком.

Если такую операцию можно реализовать то вопрос второй: ниже сделать 5-10 строчек в которые Excel будет подставлять возможные варианты) ведь Ивановых может быть в базе несколько.

пс. Я так думаю, что это можно сделать через User form но я пока еще не силен в этом, пока только запись макросов )))

vasiliy74

Цитата:

Цитата:ДайПодстроку

слушай поставил я эту функцию и у меня Excel стал ресурсов есть намного больше с чем это может быть связанно?

Excel вывывает перерасчет всех ячеек с этой функцией при изменениях в структуре листа - удаление/вставка строк/столбцов и при изменениях в модулях VBA

если найду как с этим боротся - отпишусь

есть такая проблема, в Excel2003 при открытии нескольких документов меняется цвет ячеек, если открывать ручками эксель для каждой страницы новый, то такого не наблюдается.

Такая задача: есть таблица на несколько тысяч строк. Таблица заявок. Есть столбцы с датой заявки, областью заявки, темой заявки, именем инициатора заявки, именем исполнителя. Необходимо в новых таблицах сделать статистику по скажем месяцам. В одной таблице будут строки с именами исполнителей и столбцы с месяцами: сколько раз за такой то месяц такой то человек был исполнителеи заявки. К примеру. Другая таблица будет по Инициаторам заявок. Можно это сделать средствами Екселя? Без переноса этого всего в Аксес какой-нить, чтобы реализовать это sql-запросами?

можно ли хранить в ячейках изображения?

svs123456789
Давно известно, что Word и Excel делали независимые команды разработчиков.
Как ни странно, в таблицах Word вставка рисунка (и последующая работа с ним) на ПОРЯДОК логичнее, чем в Excel...
Короче, в Excel можно вставлять изображения... И это изображение привязывается
к ячейке...

выделите ячейку, "Вставить / Рисунок / Из файла - укажите файл с изображением"

(или, что тоже самое, выполнить такой макрос:
Range("E4").Select
ActiveSheet.Pictures.Insert( _
"C:\108.jpeg").Select разумеется, путь к картинке поставьте свой...)

=СЧЁТЕСЛИ( ??????

Имеем Лист1 В нем список сотрудников приготовленный для печати (подкорректировал так, чтобы на каждом листе при распечатке выводилась отдельная бригада рабочих.
Естественно повторяется заголовок (шапка) по всей высоте столбца. Есть и пустые строки.
Вобщем надо подсчитать количество непустых ячеек не имеющих "Фамилия"
=СЧЁТЕСЛИ(A:A;<>"Фамилия") так не получается, ругается.

добавил:
У меня получилось так: =65536-СЧИТАТЬПУСТОТЫ(D:D)-СЧЁТЕСЛИ(D:D;"ФАМИЛИЯ")

CEMEH
ну, можете попробовать ещё такой вариант (это если нужен НЕ ВЕСЬ столбец, а только диапазон
{=СУММ(ЕСЛИ(A1:A9<>"фамилия";1;0)*НЕ(ЕПУСТО(A1:A9)))}
обратите внимание - это формулы МАССИВА!
Формула массива создается так же, как и другие формулы, с той разницей, что для ввода такой формулы используются клавиши CTRL+SHIFT+ENTER.

de_Marko

Цитата:

колько раз за такой то месяц такой то человек был исполнителеи заявки

1) посмотрите в сторону Сводного отчёта (сам я его плохо знаю, поэтому конкретнее подсказать не могу (пока, по крайней мере ;-))
2) sql запросы можно использовать и в Excel (только с определёнными ограничениями и осторожно)
3) на крайний случай - можно это сделать через формулы или через программирование на VBA...

К моему сообщению написанному чуть выше:
=СЧЁТЗ(D:D)-СЧЁТЕСЛИ(D:D;"ФАМИЛИЯ")
Из количества непустых ячеек вычитаем количество с "Фамилия"

построена точечная диаграмма (6 точек, для каждой точки указаны координаты ХиY).
по этим точкам проведена линия тренда (линейная)
как отложить значение Х на график и узнать значение Y, при этом линии пересечения графика с координатными прямыми должны быть видны.
Силами Excel2003 такое возможно?

DavidKATS
отвечаю на вопрос, заданный в другом топике...


Цитата:

И вот сразу вопрос: МОЖНО ЛИ сделать так, чтобы книга работала в многопользовательском режиме? То есть, чтобы одновременно с рабочей книгой по сети работало несколько пользователей. Причем, чтобы каждый из них мог бы вносить в нее изменения. чтобы программа не сообщала, что мол книга уже используется, так что юзай ее теперь только для чтения.

возможно меню - "Сервис" - "Доступ к книге..." - [ ] Разрешить совместный доступ

читай доки - они рулез!!
[more=из справки Excel "Совместное использование книги"]
Совместное использование книги
Создайте книгу, которая должна быть доступна для изменения нескольким пользователям, и введите в нее данные для совместной работы.
Если требуется использовать одну из следующих функций, настройте ее сейчас: объединение ячеек, условные форматы, проверка данных, диаграммы, рисунки, объекты (включая графические объекты), гиперссылки, сценарии, структуры, итоги, таблицы данных, отчеты сводной таблицы, защита книги и листов, макросы. Эти функции нельзя включить или изменить после предоставления книги для совместной работы.

В меню Сервис выберите команду Доступ к книге и откройте вкладку Правка.
Установите флажок Разрешить совместный доступ и нажмите кнопку OK.
Если будет предложено, сохраните книгу.
В меню Файл выберите команду Сохранить как и сохраните книгу на сетевом ресурсе, доступном другим пользователям. Используйте для этого сетевую папку, а не веб-сервер.
Проверьте все ссылки на другие книги и документы и при необходимости исправьте их.
[/more]
от себя добавлю - тут народ жаловался, что в случае активной совместной работы, какие-то данный терялись... ;-(((
Так что, может быть Вам стоит подумать о нормальной СУБД и программе доступа к данным?... Впрочем, дерзайте - может Excel и удовлетворит все Ваши потребности!