» Excel FAQ (часть 3)

Ivanov Ivan

Цитата:

Как сделать, чтобы заголовки столбцов оставались на месте, а строчки перемещались, то есть чтобы не использовать полосу прокрутки, а прокручивать список просто колёсиком мышки?

Команда меню Окно/Закрепить области

Доброе время суток.
Подскажите пожалуйста следующие:
есть обьединенная ячейка, например орбьединяющая строки 1-3 листа Excel. Как узнать номер первой строки, которая обьеденена в эту ячейку? Дело в том что я могу перейти в эту ячейку с первой, второй и третей строки (Ячейка обьединяет только 1 столбец) и в этом случае свойство ROW ячейки будет различно. Как определить первую строку?

Troitsky
ясно.


Цитата:

И еще: как сделать, чтобы ВСЕ ячейки в таблице растягивались по содержимому?

Ivanov Ivan

Цитата:

И еще: как сделать, чтобы ВСЕ ячейки в таблице растягивались по содержимому?

формат - столбец - автоподбор ширины. Предварительно можно хлопнуть по верхней левой ячейке (ряд номеров ячеек) для выделения всего листа.
//с такими примитивными вопросами, мож лучше хелп почитать? //

Вот такой список фильмов

Цитата:

Spiral (TV) / Спираль
Год: 2002
Жанр: Аниме
Качество: Оригинал
Страна: Япония
Добавлен: Сегодня
Witch Hunter Robin (TV) / Робин - охотница на ведьм
<Братство Соломона> имеет свои филиалы по всему миру, в том числе и в Японии. Группа молодых людей под патронажем своего босса расследует преступления, совершенные ведьмаками - ...
Год: 2002
Жанр: Аниме
Качество: Оригинал
Страна: Япония
Добавлен: Вчера
Neon Genesis Evangelion - Death & Rebirth (Movie) / Евангелион - Смерть и перерождение
В 2001 году группа учёных совершала экспедицию в Антарктиду, где было найдено гигантское существо из света, которое они считали первым Ангелом, Адамом. Контакт с Ангелом привёл ...
Год: 1997
Жанр: Аниме, Фантастика
Качество: Оригинал
Страна: Япония
Добавлен: На неделе
Interlude (OVA) / Интерлюдия
Жизнь четвёрки неразлучных друзей-старшеклассников весела и беззаботна - продремав на скучном уроке, можно махнуть после занятий в караоке-бар, лакомясь любимым клубничным молоком. Однако юного Мадако тревожат ...
Год: 2004
Жанр: Аниме
Качество: Оригинал
Страна: Япония
Добавлен: 22.06.2007
Ah! My Goddess (TV) / Моя богиня! (сезон первый)
После окончания школы Кэйити, добрейшей души парень, скромняга и тихоня, приехал из провинции в большой город учиться в институте, мечтая сменить серые школьные будни на весёлую ...
Год: 2005
Жанр: Аниме
Качество: Оригинал
Страна: Япония
Добавлен: 22.06.2007

Текст "описание" в отдельной строке, поэтому тут ничего делать не надо

Думаю ответы на эти вопросы уже есть на форуме, но я не пойму как построить поисковый запрос, чтобы найти то что ищу
Нужно сделать следующее:
1. Удалить строки "Добавлен:..."
Хотя может быть это можно будет использовать как флаг того, что под "добавлен" идет название фильма
После того как это будет сделанно образуются группы текста из:

Цитата:

Название
Описание
Год
Жанр
Качество
Страна

Это все в строках и между каждой группой строк будет пустая строка (либо "добавлен...")
2.1 Разнести "Spiral (TV) / Спираль" до и после "/" в разные столбцы, дабы было русское и английское название в разных слобцах
2.2 Строки сделать столбцами, чтобы было
Spiral (TV) Спираль 2002 Аниме Оригинал Япония

Vendor
Цитата:

связи с этим вопрос:
Есть строка 1, 2, 3
как сделать так чтобы каждая цифра (текст) из строки была в своем столбце

Строка - это одна ячейка? Тогда просто встал в эту ячейку и в меню Данные -> Текст по столбцам. С Разделителями. В качестве разделителя ,

Цитата:

А если несколько строк

Без разницы. Тогда выделяешь несколько строк.

Цитата:

Строка - это одна ячейка? Тогда просто встал в эту ячейку и в меню Данные -> Текст по столбцам. С Разделителями. В качестве разделителя ,

Мне нужно чтобы это как-то автоматом делалось. Т.е. формулой или еще как. Ибо 50.000 строк так делать ОООЧЕНЬ долго!

Цитата:

Мне нужно чтобы это как-то автоматом делалось. Т.е. формулой или еще как. Ибо 50.000 строк так делать ОООЧЕНЬ долго!

я обратил внимание, что последняя строка блока оканчивается на "добавлен". Через замену с регекспами (в ворде или текст.редакторе, который юзаешь) меняешь содержимое строки, начинающейся с этого слова, на .. скажем, <p>. После чего в том же ворде (просто там помню символ разрыва абзаца/переноса строки - в текст формает забыл) жмешь замену:
поиск ^p замена ^t (символ табуляции)
поиск <p> замена ^p

Далее - ктрл А, меню-таблица - преобразить в таблицу - символ-разделитель = знак табуляции.
А после эту таблицу выделяешь и кидаеш в любую ячейку экселя, которая станет левым верхним углом твоей таблицы в ёкселе.

всё.

Замена с регекспами в ворде - в помощи есть список символов. Что-то типа
поиск: ^pдобавлен: [-15*]^p
замена: p^<p>^p
(типа, блок не более 15 любых символов)

////сорри, если хотелось решение, как сделать не где угодно. а именно в ёкселе - подумал, ехать важнее чем шашечки

я нашел другое решение. Правда гипер сложнее чем ты предложил ))

1. сначала вытаскиваем название фильма на англ/рус
ЕСЛИ(ЛЕВСИМВ(A1;9)="Добавлен:";A2;"")

2. потом разделяем англ/рус по разным столбцам такими формулами
один столбец
ЕСЛИ(НАЙТИ("/";A2)>0;ЛЕВСИМВ(A2;НАЙТИ("/";A2)-2);3)
другой столбец
=ЕСЛИ(НАЙТИ("/";A2)>0;ПРАВСИМВ(A2;ДЛСТР(A2)-НАЙТИ("/";A2)-1);"")

3. соответственно вытаскивает жанр, год и тд
ЕСЛИ(ЛЕВСИМВ(A2;5)="Жанр:";ПРАВСИМВ(A2;ДЛСТР(A2)-6);"")
ТЕКСТ(ЕСЛИ(ЛЕВСИМВ(A2;4)="Год:";ПРАВСИМВ(A2;4);"");0)

Это геморное решение. Но все же + из-за этого условия с "/" не получается вытащить фильмы полностью на русском языке, которые априори без "/". НО это я что-нибудь придумаю

У меня такая трабла:
Поставлен 2007 русский офис (Энтерпрайз). Фиха вот: в чем при попытке создания или редактирования в эксель правил условного форматирования вылезает окно "недостаточно системных ресурсов для полного вывода на экран." и ничего не происходит. Странно... Причем в разных дистрах и на разных компах одно и тоже. Может кто знает как побороть?

bredonosec
Кароче по твоему методу у меня ничего не получилось. Может я бабобаб? Точнее получилось, но не то:
1. Это все в одной строке, т.е. куча столбцов в одной строке и как это разделить обратно - хз
2. там где не надо надписей "год:","Жанр:" и тд они есть и как их теперь убрать - хз
3. отделить русские названия фильмов от английских тоже - хз
4. так как все таки описания бывают в двух строках, то столбцы все перепутались в конечном итоге

Вывод: Немного не тот метод. Хотя какое-то продвижение есть.

Кстати, по поводу моего метода. Получилось, что есть 5 столбцов нужных мне и в каждом из них есть то что мне нужно, но с одим условием - между строками расстояние в 5 пустых строк... а это мне не нужно. Хотя можно сделать сортировку и тогда они сделаются все без пробелов, но т.к. нужные мне данные не всегда в одной строке (это связанно с тем, что описание не в одной строке, а иногда в двух)...

Наверное, придется ручками править.

Vendor

Цитата:

и в каждом из них есть то что мне нужно, но с одим условием - между строками расстояние в 5 пустых строк... а
...
Наверное, придется ручками править.

А Вы вариант использования макроса совсем не рассматриваете?
Можно макросом пустые строчки удалить...

Vendor
Чтоб небыло пустых строк попробуй данные - фильтр автофильтр -> условие отображать непустые

например, вот [more=этот макрос]

Код:
Sub DeleEmpty()
Row1 = ThisWorkbook.ActiveSheet.UsedRange.Row
Row2 = Row1 + ThisWorkbook.ActiveSheet.UsedRange.Rows.Count - 1
i = Row1
While i <= Row2
If IsEmpty(Cells(i, 1&).Value) And IsEmpty(Cells(i, 2&).Value) _
And IsEmpty(Cells(i, 3&).Value) And IsEmpty(Cells(i, 4&).Value) _
And IsEmpty(Cells(i, 5&).Value) Then
Cells(i, 1).EntireRow.Delete
Row2 = Row2 - 1
Else
i = i + 1
End If
Wend

End Sub

Всем спасибо! Ответа для себя не нашел, но зато был импульс сделать самому )) Сделал через жопу, но все же сделал же! Буду иметь ввиду этот форум. Очень даже мудрые люди и желающие помочь. Ну и сам, само собой, буду помогать

Добавлено:
получилось! Супер!
Возник другой вопрос

Помогите пожалуйста

Вот так получилось


Теперь с помощью фильтра можно выбрать то что нужно. Но если выбираешь в столбце жанр1 то показываются те фильмы у которых этот жанр указан в этом слотбце, а если в жанр2, то соответственно которые в жанр2

Получается смешно. Т.к. выбрать одновременно во всех 3 столбцах невозможно (по-крайней мере в 2007 экселе), то получается гемор.

У меня есть решение: Удалить все, например, комедии из столбца "жанр2" и поместить их в столбец "жанр1" и тогда не будет путаницы, что написанно выше. Но будет другое - в столбце жанр2 не будет показываться "комедии", а будут только в столбце "жанр1". А это тоже гемор

Есть ли какое-нибудь решение этому вопросу? Может быть вынести эти столбцы за границы главной таблицы и каким-то образом (может через макрос или скрипт) сделать кнопочку, чтобы с помощью неё можно было посмотреть все "комедии" по всем трем столбцам.

П.С. Мне не сложно удалить все комедии из столбца жанр2, но нафига это нужно я не понимаю. Это особого удобства не добавит...

Vendor
Есть такая волшебная штука в Excel, называется "Расширенный фильтр"
(находится меню Данные / Фильтр / Расширенный фильтр... )
почитайте по нему справку - там легко организовать фильтр не и только по одному столбцу, и даже по разным значениям в одном и том же столбце. Разобраться просто (хотя и не очевидно ;-)). Не получится - пишите, общество поможет.

[OFFTOP]
А вообще, вот хоть убейте меня, но эта задача не для Excel! Чем вам не нравится тот же бесплатный Ant Movie Catalog ? Или платный All My Movies
А в Excel удобно выгружать отчёты, отдавать список на сторону, выкладывать на Web...
но не использовать в качестве СУБД. Хотя.. опять же - можно нарисовать форму ввода,
форму поиска (+макрос) с отображением результатов на отдельном листе (с возможнотью печати) -это будет уже что-то...

Цитата:

А вообще, вот хоть убейте меня, но эта задача не для Excel! Чем вам не нравится тот же бесплатный Ant Movie Catalog ? Или платный All My Movies
А в Excel удобно выгружать отчёты, отдавать список на сторону, выкладывать на Web...
но не использовать в качестве СУБД. Хотя.. опять же - можно нарисовать форму ввода,
форму поиска (+макрос) с отображением результатов на отдельном листе (с возможнотью печати) -это будет уже что-то...

Ага только это я все сниферис (с помощью парсера) с сайта где это все можно скачать. Поэтому каталогизаторы тут не подойдут. А вбивать ручками 10000 позиций (+ данные по каждому фильму) это уж извините...
Был бы доступ к хардам, я бы так и сделал, поверьте ))

Vendor

Цитата:

Ага только это я все сниферис (с помощью парсера) с сайта где это все можно скачать. Поэтому каталогизаторы тут не подойдут. А вбивать ручками 10000 позиций (+ данные по каждому фильму) это уж извините...

ой. в оффтоп уходим окончательно.... ;-(((
Кто говорит о ручном вводе данных!?!?!?
В том же AntMoviecatalog есть импорт из нескольких разных форматов баз данных.
в том числе из CSV (это Excel файл - comma separated values) - с настраиваемым набором полей...

Ладно. Теперь про Excel - расширенный фильтр помог?

Цитата:

SERGE_BLIZNUK

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

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

П.с. (оффтоп)

Цитата:

В том же AntMoviecatalog есть импорт из нескольких разных форматов баз данных.
в том числе из CSV (это Excel файл - comma separated values) - с настраиваемым набором полей...

Для того чтобы сделать csv надо хотябы бэйсик знать или что-то работающее с файловыми массивами, чтобы был правильный формат для импорта... А я не знаю никаких языков к моему наивеличайшему сожалению.

Vendor

Цитата:

было бы сделать список из которого выбираешь. Видимо это нужно как то макросами делать или что-то подобное.

выделите из Вашего файла пару десятков строк и сохраните в отдельный файл.
запакуйте его. архив выложите на любой бесплатный хостинг (rapidshare.com mytempdir.com zalil.ru ) полученную ссылочку опубликуйте.
Если не я, так кто-нибудь из мэтров помогу вам сделать так, чтобы при выборе жанра из списка - отфильтровывались записи, у которых совпал Жанр1 или Жанр2 или Жанр3


Цитата:

Для того чтобы сделать csv надо хотябы бэйсик знать

извините, думал, что это очевидно. Не надо ничего программировать!
В Excel - Файл / Сохранить Как...
(внизу, там где тип файла, выбираете CSV (разделители - запятые)
всё. ;-)))

SERGE_BLIZNUK
я уже боюсь Вы так "конкретно" к этому подошли

вообще у меня все получилось. Все таки не зря в сумме сижу за этой штукой уже как часов 4-5. Быстрее не получилось как-то... Я решил оставить как есть жанры. Жанры идут как нужно т.е. Анимэ, Боевик (этя для аниме) или боевик, Анимэ (это для боевиков). Таким образом фильмы повторяются по 3-4 раза, в зависимости от того сколько жанров в нем, но все равно так наглядно и достаточно удобно. Или это не правильно? Как думаете?

У меня получилось что между строками с данными есть от 5 до 6 пустых строк.

Они удаляются при помощи автофильтра и снятии галочки "обображать пустые строки". Но при этом невозможно пользоваться фильтром в этом стобце. Попробовал банально сделать копи-паст - выдает ошибку.

Буду благодарен, если ответите на последний (я надеюсь) вопрос: Как удалить пустые строки?

Вот как было - http://slil.ru/24615287 , Может быть все таки найдется лучший вариант

А вот как стало, после моих преобразований - http://slil.ru/24615303 . Это который с пустыми строками и жанрами в одном столбце. Вообще было бы совсем приятно, если бы можно было бы сделать как-то поиск и сортировку по этому варианту, потому что 3 столбца жанров это уже урезанный вариант (у некоторых фильмов по 4-5 жанров)

А по этой ссылке можно скачтаь кусок оригинала (с наглядной иллюстрацией того, что у фильмов бывает и по 5 жанров ) - http://slil.ru/24615396

Мне попался хитрый xls-файл - не дает копировать лист (там всего 1 лист), говорит что нельзя добавить примечание в режиме совместного доступа, никто с этим файлом параллельно не работает, комп даже к сети не подключен. И не в каждую ячейку в этом файле можно поставить курсор. Кроме того на линейке не активны кнопки начертания шрифтов и пр., связанные с редактированием. Файл судя по-всему создавался в Эксель 2003 или более ранней версии. Что за дела с этим файлом? Там не секретная инфа , защищать нечего... Мне нужно скопировать лист из этого файла, чтобы его редактировать.
Я работаю в Эксель 2007.

lucky_Luk
1) в Excel 2003 было в пункте Сервис / Доступ к книге...
чекбокс - "Разрешить совместную работу" - попробуйте снять
2) так же посмотреть есть защита на лист/ячейки - попытаться снять её...

Vendor
я ещё посмотрю, что с этим можно сделать.
Но, скорее всего, отвечу уже завтра.
А пока воспользуйтесь макросом для удаления пустых строк.
[more=Сам макрос]
Код: [no]

Sub DeleteEmptyLines()
Dim i, Row1, Row2 As Integer
Row1 = ThisWorkbook.ActiveSheet.UsedRange.Row
Row2 = Row1 + ThisWorkbook.ActiveSheet.UsedRange.Rows.Count - 1
i = Row1
While i <= Row2
If (IsEmpty(Cells(i, 1&).Value) Or _
(TypeName(Cells(i, 1&).Value) = "String" _
And (Trim(Cells(i, 1&).Value) = ""))) And _
(IsEmpty(Cells(i, 2&).Value) Or _
(TypeName(Cells(i, 2&).Value) = "String" _
And (Trim(Cells(i, 2&).Value) = ""))) And _
(IsEmpty(Cells(i, 3&).Value) Or _
(TypeName(Cells(i, 3&).Value) = "String" _
And (Trim(Cells(i, 3&).Value) = ""))) _
Then
Cells(i, 1).EntireRow.Delete
Row2 = Row2 - 1
Else
i = i + 1
End If
Wend

End Sub

[/no]

SERGE_BLIZNUK
При выполнении макроса выдает ошибку:

Цитата:

Run-time error - '6':
Overflow

Если ткнуть на дебаг, то выделяется строка
"Row2 = Row1 + ThisWorkbook.ActiveSheet.UsedRange.Rows.Count - 1"

Вообще, мне все равно как будут жанры распологаться, главное, чтобы было видно какой жанр у какого фильма (это как раз удобнее, когда все жанры в одном столбце) и, второе, чтобы можно было сделать выборку по жанрам (а вот тут я не уверен, что при жанрах в одном столбце это вообще возможно сделать. Хотя опыт мне подсказывает "невозможное возможно" )

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

П.С. В экселе так много интересного, я даже и не ожидал! Хотя работаю в нем уже давно и кстати говоря, считал что знаю его хорошо. Эх, как же я был наивен

SERGE_BLIZNUK

Цитата:

1) в Excel 2003 было в пункте Сервис / Доступ к книге...
чекбокс - "Разрешить совместную работу" - попробуйте снять
2) так же посмотреть есть защита на лист/ячейки - попытаться снять её...

А где это все находится в Эксель 2007?

Vendor

Цитата:

Если ткнуть на дебаг, то выделяется строка
"Row2 = Row1 + ThisWorkbook.ActiveSheet.UsedRange.Rows.Count - 1"

ваша правда. Совсем выпустил из виду, что у вас много строчек...
конечно, после драки нечего кулаками махать, но, так сказать, в целях оправдания [more=исправленный и дополненный макрос]

Код:
Sub DeleteEmptyLines()
Dim i, Row1, Row2 As Long
Application.ScreenUpdating = False
Application.EnableEvents = False
Row1 = ThisWorkbook.ActiveSheet.UsedRange.Row
Row2 = Row1 + ThisWorkbook.ActiveSheet.UsedRange.Rows.Count - 1
i = Row1
While i <= Row2
If (IsEmpty(Cells(i, 1&).Value) Or _
(TypeName(Cells(i, 1&).Value) = "String" _
And (Trim(Cells(i, 1&).Value) = ""))) And _
(IsEmpty(Cells(i, 2&).Value) Or _
(TypeName(Cells(i, 2&).Value) = "String" _
And (Trim(Cells(i, 2&).Value) = ""))) And _
(IsEmpty(Cells(i, 3&).Value) Or _
(TypeName(Cells(i, 3&).Value) = "String" _
And (Trim(Cells(i, 3&).Value) = ""))) _
Then
Cells(i, 1).EntireRow.Delete
Row2 = Row2 - 1
Else
i = i + 1
End If
Wend

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Нужно сделать так, чтобы если число в определенной ячейке меньше нуля, то другая ячейка выделяется одним цветом, а если больше нуля, то другим... Как составить условие знаю, а вот как управлять цветом ячейки (и, желательно, цветом шрифта в этой ячейке) – нет... Желательно сделать это с помощью формул, а не с помощью VBA. Кто-нибудь знает как решить задачку? Спасибо.

griin
Формат-Условное форматирование

lucky_Luk
Можно поставить Classic menu для Office 2007
http://dump.ru/files/f/f667831439/
Очень удобно. Не надо заморачиваться с новым интерфейсом.

Ммм... Условное форматирование смотрел, не подходит... Или я не до конца с ним разобрался... Как действие этого условного форматирования распространить на всю стороку, а не на текущую ячейку? Как сохранить это форматирование, чтобы его на приходилось каждый раз создавать заново?