Вопрос
А какие вообще программы работают с таблицами? Если кто чего знает, поделитесь, пожалуйста. А то кроме Екселя ничего вспомнить не могу…
Ответ
Supercalc, Lotus123, Staroffice, Quatro Pro
Artem, Warlock
Works — но возможно им уже никто не пользуется :))
******
OpenOffice Calc, Kspread
Вопрос
Как сделать, чтобы в соседней ячейке суммировалось все, что вводится в предыдущую.
При написании формулы типа =СУММ… выводится сообщение о циклической ссылке, а при активизатиции "интерации" ( Сервис — Параметры — Вычисления) в нужную ячейку попадает все из других ячеек.
Ответ
Делается это простой формулой =СУММ(диапазон).
Просто введите в суммирующую ячейку "=СУММ(", затем либо мышью, либо клавишами (Up, Down, Left, Right) выделите нужный диапазон суммирования и нажмите Enter. Excel сам закроет скобку и выдаст результат в ячейке. Кстати, если будете выделять клавишами, чтобы выделить массив ячеек пользуйтесь Shift'ом.
Из конференции Expert_FAQ
Вопрос
Иногда бывает так (не могу отследить, отчего), что в ячейках вместо значений показываются формулы. Никак не получается заставить Ексель показывать результат формулы, а не ее саму.
Только скопировав содержимое ячеек в новую книгу, проблема исчезает. Но хочется все-таки как-то обойтись без такого хирургического вмешательства.
Ответ
Зайди в свойства (формат) ячейки (по правому клику), там есть такая птичка, что показывать значение или формулу.
Из конференции Expert_FAQ
В настройках Excel есть параметр отвечающий за то, что выводить на экран, значения или формулы. Добраться до него можно следующим образом. Зайти в меню Сервис, затем в Параметры, там выбрать вкладку Вид. Там в Параметрах окна, есть параметр Формулы. Если слева от него стоит галочка, то отображаются формулы, если нет галочки, то отображаются значения.
Также можно переключать этот режим нажав клавиши [Ctrl] + [˜] ,
где ˜ — тильда, клавиша под Esc.
djonplayer
Вопрос
Как сделать в Excel, чтобы верхняя строка таблицы всегда была на экране, т.е. при скроллинге не поднималась вместе со всей таблицей вверх.
Ответ
Есть два варианта:
1 — На вертикальной прокрутке листа немного выше верхней стрелки (та, что выше бегунка) стрелка курсора превращается в две горизонтальные полоски со стрелками верх-низ. Левой кнопкой тянешь мышу вниз и получаешь лист разделенный надвое с возможностью прокручивать каждый фрейм автономно.
2 — Меню «Сервис — Настройка, вкладка «Команды», категория «Окна и справка» есть кнопка «Закрепить области» Перетягиваешь ее оттуда на панель инструментов, что б всегда была под рукой ;)). Затем в своей таблице ниже и правее строки, которая должна быть постоянно видна выделяешь ячейку и нажимаешь эту кнопку.
okna
Помечаешь стоку ниже нужной, window-freeze panes.
sikharuli
Вопрос
При печати с Excel таблица с одного листа печатается нормально, а со второго сжимается. Где выставить настройки печати?
Ответ
Проблему с расположением данных на странице можно решить следующим способом:
файл — предварительный просмотр — разметка страницы (вернуть обычный вид: Файл — предварительный просмотр — обычный режим) или Вид — Разметка страницы (вернуть стандартный вид можно там же: Вид — обычный). В результате Вы увидите вашу страницу, разбитую синими полосами: перетаскивая их, вы установите границы страниц, маштаб Excel подберет сам.
Вопрос
При печати с Excel таблица с одного листа печатается нормально, а со второго сжимается. На обоих шрифт 8 на первом таблица растянута на весь лист на втором нет. Где выставить эти настройки печати? Стоит Exсel 97.
Ответ
Проблему с расположением данных на странице можно решить следующим способом:
Файл — Предварительный просмотр — Разметка страницы (в открывшемся окне на верхней панели)
в результате Вы увидите вашу страницу, разбитую синими полосами, перетаскивая их вы установите границы страниц, масштаб Эксель подберет сам.
Чтобы перейти в обычный режим необходимо повторить:
Файл — Предварительный просмотр — Обычный режим (на месте надписи «разметка страницы»
Имеется ввиду что параметры страницы уже выставлены (границы, альбомная или книжная и тд.
Из конференции Expert_FAQ
Вопрос
В ходе работы с Excel 97 столкнулся с необходимостью написания макроса на VisualBasic. Однако я с ним не знаком. Просьба по возможности подсказать, где можно найти руководство по использованию VB для Excel с самых азов.
Ответ
Довольно много есть по ссылке:
www.infocity.kiev.ua
Даже что-то вроде VBA для детей :))
Из конференции Expert_FAQ
Вопрос
Есть ли в EXCEL XP что-то подобное, как в 2000'ом гонки или в 98'ом 3D движОК?
Ответ
Еще при выпуске Windows XP и Office XP Microsoft клятвенно заверяла, что в этих продуктах не будет никаких «яиц» («Easter Eags»).
Из конференции Expert_FAQ
Вопрос
Есть ли способ запретить изменение ширины столбцов и высоты строк?
Ответ
Меню — Сервис — Защита — Защитить лист.
Если при этом нужно сохранить возможность редактирования ячеек, то перед включением защиты листа нужно отключить для нужных ячеек включение защиты — Меню — Формат — Ячейка… — Защита — Защищать — откл.
Из конференции Expert_FAQ
Вопрос
Имеется книга из 26 листов, переход между которыми несколько затруднителен. Хотелось бы создать в книге первый лист на который поместить кнопочки, при нажатии на одну из которых происходила автоматическая активизация нужного листа. Как это сделать практически?
Ответ
Надо записать мАкрос с меню «Вид» — «Панели_инструментов» — «Visual_Basic».
Private Sub Makros_MOVE_LIST_2()
Sheets(«Лист2»).Select
End Sub
А затем: создать кнопки с помощью меню «Вид» — «Панели_инструментов» — «Формы».
И назначить им макросы.
Из конференции Expert_FAQ
Можно это сделать еще с помощью гиперссылок (Вставка — Гиперссылка).
amakeev
Создаеш код, вместо N подставляеш либо число (без кавычек), либо строковое значение «Имя листа»
Public Sub Workbook_Open()
Worksheets(N).Activate
End Sub
Андерсон
Вопрос
Как мне сделать так, чтобы при выполнении определенного условия (напр. B4=0) строка (напр.4) с этой ячейкой скрывалась бы автоматически?
Ответ
Sub Макрос7()
'
'
Sheets("Лист1").Select 'выбор листа
Range("b4").Select 'выбор проверяемой ячейки
If Range("b4") = 0 Then 'сравнение с нулем
Rows("4:4").Select 'выбор строки
Selection.EntireRow.Hidden = True 'скрыть строку
End If
End Sub
и дальше выполни Запуск — Запуск подпрограммы
Анатолий Киюк
Const glCol As Long = 2 \' столбец B
Const gbAllHide As Boolean = False
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Rows.Count = 1 And .Columns.Count = 1
And .Column = glCol And .FormulaR1C1 = \"0\"
Then
If gbAllHide Then
Application.ScreenUpdating = False
With ActiveSheet.UsedRange.Rows
Dim lR As Long
For lR = .Row To .Row + .Count — 1
If Cells(lR,
glCol).FormulaR1C1 = \"0\" And Not Rows(lR).Hidden
Then
Rows(lR).Hidden = True
End If
Next lR
End With
Application.ScreenUpdating = True
Else
.Rows.Hidden = True
End If
End If
End With
End Sub
Теперь при вводе 0 в любую строку столбца 2 эта строка (а при включенном режиме gbAllHide — все строки, содержащие 0) будет спрятана автоматически.
Priam
Вопрос
Мне нужно создать книгу Excel с 30-тью листами внутри. Можно ли этот процесс как-нибудь автоматизировать?
Ответ
В версии 2000 это просто: Сервис — Параметры — вкладка «Общие» — Листов в новой книге (указать нужное количество) — 30
Narzan
Вопрос
Имеется табличка в Excel, содержащая помимо прочего столбцы «Код товара» и «Описание товара». Так вот вопрос, можно ли сделать средствами Excel чтобы при вводе значения в столбец «Код товара» в столбце «Описание товара» происходило автоматическое заполнение из базы, содержащейся либо в этой же книге, но на другом листе, либо из другого файла?
Ответ
Можно. В свое время я делал такое заполнение следующим образом: на отдельном листе размещал список из двух столбцов — код и наименования, на рабочем листе в колонке, где должно наименование появиться, писал формулу.
Функция называется ВПР. Ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы. В данном случае ищет артикул в базе и, если находит, то подставляет значение, соответствующее номеру столбца, который мы указываем.
Евгений
Вопрос
Как в Excel (XP, 2000) поменять обозначения столбцов с букв на цифры (с «A,B,C…» на «1,2,3…») и наоборот.
Ответ
Это дело переключается вместе с изменением стиля ссылок — Сервис-Настройки-Общие-Стиль ссылок R1C1
При включенном — будут числа в заголовках столбцов, но и ссылки в формулах тоже будут отображаться в стиле R1C1.
При выключенном — будут буквы в заголовках столбцов и ссылки в формулах в стиле A1
Из конференции Expert_FAQ
Вопрос
Как установить наиболее часто повторяющееся значение в колонке таблицы средствами VBA for Excel и вывести его в текстовое поле TextBox?
Ответ
Dim vMod As Variant, rng As Range
Set rng = Range("A1:A200")
vMod = Application.WorksheetFunction.Mode(rng)
Me.TextBox1.Value = vMod
Проблема только в том, чтобы определить обрабатываемый диапазон. Но если под таблицей ничего нет, то сойдет и 65535 как номер последней строки, т.е. примерно так: Set rng = Range("A1:A65535")
Leshek
Вопрос
Имею книгу.xls с более чем сотней листов. И еще имею макрос, который нужно применить к каждому листу. Щелкать по каждой странице и нажимать Alt+F8 не очень хочется. Как исхитриться и написать макрос, который перебрал бы все листы и выполнил другой макрос на каждом листе?
Ответ
Делается это все довольно просто, как видно из следующего фрагмента кода:
Sub Macro1()
' это макрос, который необходимо вызвать для каждого листа в рабочей книге
End Sub
Sub MacroCaller()
' следующий цикл выполнится для всех листов в рабочей книге
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Activate ' активировать лист
Macro1 ' вызвать макрос, работающий с активным листом
Next Sheet
End Sub
Таким образом, независимо от того, сколько листов находится в рабочей книге, макрос Macro1 будет запущен для каждого листа.
Алексей.
Вопрос
Как сделать в Excel чтобы верхняя строка таблицы всегда была на экране, т.е. при скроллинге не поднималась вместе со всей таблицей вверх?
Ответ
Есть два варианта:
1 — На вертикальной прокрутке листа немного выше верхней стрелки (та, что выше бегунка) указатель курсора превращается в две горизонтальные полоски со стрелками верх-низ. Левой кнопкой тянешь мышу вниз и получаешь лист, разделенный надвое, с возможностью прокручивать каждый фрейм автономно.
2 — В меню Окно выбираешь пункт Разделить. Если не хочешь постоянно лазить в это меню, то иди в Сервис-Настройка, вкладка Команды, категория Окна и справка. Там есть кнопка Закрепить области. Перетягиваешь ее оттуда на панель инструментов, чтобы всегда была под рукой ;)). Затем в своей таблице ниже и правее строки, которая должна быть постоянно видна выделяешь ячейку и нажимаешь эту кнопку. Все!
******
А еще есть окно — Закрепить
Вопрос
Вот как в ворде назначить комбинацию клавиш я знаю. А в экселе есть стандартные средства для этого? Ну, не считая написания макросов, я имею в виду.
Ответ
Я не нашел тоже. Но создать макрос для нужной команды с вызовом по Ctrl + Literal Key нетрудно.
Хотя есть такой способ, и вы его знаете ;4)
Как известно, в случае вставки амперсанда (&) перед некоторой буквой в названии команды, эта команда будет вызываться по Alt + буква. Правда, если «нет такой буквы в этом слове», то увы…
Alex
В процедуре Auto_Open пишешь: application.onkey «комбинация», «macro_name»
AndreyT
Вопрос
Как в Excel определить последнюю ячейку в строке и столбце?
Ответ
Функция для определения последней заполненной ячейки в столбце intCol — нужный столбец (номер!), lngRow — начальная строка поиска (если не задана, поиск начинается с первой).
Function LastFullRow(intCol As Integer, _
Optional lngRow As Long = 1) As Long
Dim lngOff As Long, rng As Range
lngOff = lngRow — 1
Set rng = Cells(lngRow, intCol)
Do While rng.Offset(lngOff, 0).Value <> ""
lngOff = lngOff + 1
Loop
LastFullRow = lngOff
End Function
Функция для определения последней заполненной ячейки в строке (параметры аналогичны предыдущей)
Function LastFullColumn(lngRow As Long, _
Optional intCol As Integer = 1) As Integer
Dim intOff As Long, rng As Range
intOff = lngRow — 1
Set rng = Cells(lngRow, intCol)
Do While rng.Offset(0, intOff).Value <> ""
intOff = intOff + 1
Loop
LastFullColumn = intOff
End Function
Alex Juice
Так проще:
ActiveCell.End(xlDown).Select ' в столбце
ActiveCell.End(xlToRight).Select в строке
' вместо ActiveCell можно (Range("A1"),Cells(r,c)либо имя ячейки)
tolya
Вопрос
Как в Excel отразить на диаграмме изменение значения? Например, сегодня курс доллара 30,09 а вчера был 29,95.
Надо чтобы подпись была 30,09 (+0,14).
Ответ
Сначала строишь обычный график, с подписями, а потом запускаешь макрос. Но только потом после каждого изменения данных надо перезапускать этот макрос… вроде…
Sub SetLabels()
Dim oSr As Series
Dim oPt As Point
Dim dVal1 As Double, dVal2 As Double
ActiveChart.ApplyDataLabels xlDataLabelsShowNone
ActiveChart.ApplyDataLabels xlDataLabelsShowValue
For Each oSr In ActiveChart.SeriesCollection
dVal1 = 0
For Each oPt In oSr.Points
dVal2 = Val(oPt.DataLabel.Text)
oPt.DataLabel.Caption = Str(dVal2) + _
IIf(dVal2 > dVal1, " (+", " (") + _
Trim(Str(dVal2 — dVal1)) + ")"
dVal1 = dVal2
Next oPt
Next oSr
End Sub
есть еще коллекция ActiveChart.SeriesCollection(k).Values, тока я не разобрался, как ее размерность определить. Если б удалось — можно проще сделать:
For each oSr in ActiveChart.SeriesCollection
…
For i=1 to HZ
dVal2 = oSr.Values(i)
…
oSr.Points(i).DataLabel.Caption = …
Next i
Next oSr
Priam
Вопрос
Мне нужно значение из одной ячейки перенести в другую, потом его — же прибавить к значению в третьей ячейке, а потом обнулить первую ячейку, но только так чтобы не обнулились вторая и третья ячейки.
Ответ
Если Вы это делаете вручную, то нужно всего лишь копировать через Специальную вставку (Значения), тогда ячейки не будут связаны впрямую с исходной ячейкой, и ее можно будет редактировать как угодно.
Если же нужно это делать (полу)автоматически, то примерно так (проверено в Excel 2000. Предполагается, что первичная ячейка к началу применения макроса выделена и вместе с ней не выделены никакие другие ячейки):
Sub Exp1()
On Error GoTo err1
Dim strCell As String, rng As Range, Var As Variant
Set rng = Application.Selection
'Вместо следующей строки можно вставить strCell=Range("A3"),
'если известно, что значение переносится в ячейку А3
strCell = InputBox("Введите адрес ячейки для переноса значения")
'На всякий случай копируем формат исходной ячейки
Selection.Copy
Range(strCell).Select
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
'Конец копирования формата
'Вставляем значение
Var = rng.Value
Range(strCell).Value = Var
'Вместо следующей строки можно вставить strCell=Range("A3"),
'если известно, что значение суммируется с ячейкой А3
strCell = InputBox("Введите адрес ячейки для суммирования")
'Прибавляем к ячейке
Range(strCell).Value = Var + Range(strCell).Value
'Обнуляем первую ячейку
rng.Value = 0
'Или вообще очищаем первую ячейку — тогда вместо нуля поставить ""
Exit Sub
'В случае ошибки в адресах ячейки просим повторить ввод
err1:
If Err.Number = 1004 Then
strCell = InputBox("Введите правильный адрес ячейки")
Resume
End If
End Sub
Alex
Вопрос
Имеется массив, где первый столбец — клиенты, второй — наименование полученной ими продукции, третий — кол-во кг.
Как с помощью Exсel можно получить данные не только о количестве проданной продукции одного вида и не только о количестве полученного товара одним клиентом, а завязать это формулой так, чтобы получить данные на конкретного клиента по конкретному товару?
Ответ
Несколько способов имеется:
Выделяешь самую первую строку, где у тебя «шапка» таблицы (где написано «Клиент», «продукция», «масса» или типа того). Выбираешь меню ДАННЫЕ — ФИЛЬТР — АВТОФИЛЬТР. Теперь ты можешь в первой колонке выбрать любого клиента и при этом во второй выбрать любой его товар. Если тебе надо видеть сумму, то внизу, под таблицей, когда уже что-то выбрано, в ячейке под столбцом «масса» выбираешь ячейку, жмешь кнопочку «Автосумма» и выбираешь весь столбец. У тебя в этой ячейке будет формула типа:
=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C21)
Теперь ты можешь выбирать любого клиента и при этом любую его продукцию, и в этой ячейке будет сумма по выбранному клиенту по выбранному товару.
Предположим, что твои столбцы — это, соответственно, столбцы A, B, C. Тогда в столбце D пиши формулу:
=СЦЕПИТЬ(А2;" ";В2)
И получится в ней название клиента и название продукции через пробел (вместо пробела можешь поставить тире, запятую или что хочешь). Теперь выделяешь всю таблицу, сортируешь в первую очередь по клиенту, затем — по товару. И теперь выбираешь меню ДАННЫЕ-ИТОГИ, указываешь, по какому столбцу суммировать, и получится примерно такое:
ОАО «Альфа» Рыба 320
ОАО «Альфа» Мясо 540
ООО «Гамма» Куры 781
Из конференции Expert_FAQ
Вопрос
Подскажите, пожалуйста, комбинацию клавиш для удаления колонки (строки) в Excel.
Ответ
[Shift]+[Probel] — удалается вся строка
[Ctrl]+[Probel] — удаляется весь столбец
SK_REM
Вопрос
Надо в Excel97 выбирать значение ячейки из списка, который лучше хранить тоже в ячейках. Подскажите, пожалуйста, на простом примере, как это можно сделать.
Ответ
- Составляем список возможных значений в нужном месте книги — это могут быть смежные ячейки либо в столбце либо в строке по желанию.
— Если ячейка ввода этих значений находится на этом же листе, то:
— переходим в эту ячейку ввода
— Меню — Данные — Проверка — Настройка — Список — Источник — через "=" указать область списка.
— там же можно настроить, на сколько жесткая должна быть проверка значений по списку
— все
Иначе:
— перейти на лист, где будет ячейка ввода
— определить имя области списка (с этого листа), для этого:
— Меню — Вставка — Имя — Определить — назначить имя области — определить область списка (будет ссылаться на лист со списком)
— Меню — Данные — Проверка — Настройка — Список — Источник — через "=" указать имя области списка.
— там же можно настроить, на сколько жесткая должна быть проверка значений по списку
— все
Из конференции Expert_FAQ
Вопрос
Как в Visual Basic'е для Excel'а выделить ячейку, следующую, после последней заполненной в заданном столбце? Т.е., например, на Листе1 заполнены ячейки с В3 по В42, надо выделить ячейку В43.
Ответ
Sub nextcell()
Sheets("Лист1").Select
i = 1
While Cells(i, 1).Value <> ""
i = i + 1
Wend
Range("A" + Trim(Str(i))).Select
End Sub
Из конференции Expert_FAQ
Range("A1").End(xlDown).Offset(1,0).Select
dedtolya
Вопрос
Как в Visual Basic'е для Excel'а найти и выделить ячейку, содержимое которой ищется в диапазоне ячеек? Т.е. заполнены ячеки с А1 по А50 некоторыми числами, надо в этом диапазоне найти и выделить ячейкус конкретным значением (например «456»).
Ответ
Если диапазон ячеек неизвестен, то нужная ячейка ищется так:
I = 1
While (Worksheets(«Лист1»).Cells(I, 1).Value <> 10)
I = I + 1
Wend
'после отработки цикла переменная I будет равна
'номеру строки, столбца A (в данном примере)
'т.е. та ячейка в которой значение 10
Если диапазон известен, то цикл другой:
For I = 1 To 50
IF Worksheets(«Лист1»).Cells(I, 1).Value = 10 THEN J=I
Next J
Здесь цикл выполнится все 50 раз и в переменной J будет содержаться номер последней строки, где содержалось значение 10. Возможно как-то можно прерывать цикл. В классическом Бейсике это делалось оператором GOTO номер строки.
Из конференции Expert_FAQ
Вопрос
Как в VBA в Excel работать с картинками на листе?
Ответ
Sub opopp()
'
ActiveSheet.Pictures.Insert( _
"F:\Program Files\Common Files\Microsoft Shared\Clipart\cagcat50\HH01669_.wmf") _
.Select
Selection.ShapeRange.IncrementLeft 89.25
Selection.ShapeRange.IncrementTop 0.75
Selection.ShapeRange.IncrementLeft 0.75
Selection.ShapeRange.ScaleWidth 1.21, msoFalse, msoScaleFromBottomRight
Selection.ShapeRange.ScaleHeight 1.21, msoFalse, msoScaleFromTopLeft
ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:= _ "http://soobcha.lionovsky.us/"
ActiveWorkbook.Save
End Sub
А так, открываешь редактор Visual Basic и там в окошке наверху два выпадающих списка. Тебе нужен второй, там, вроде бы, и наведение курсора, и клики…
Mr.Fech
Вопрос
Как в Еxcele сделать промежуточные итоги не по значению ячейки, а по определенной формуле: допустим, если значение ячейки попадает в определенный интервал.
Ответ
Я бы завел дополнительную таблицу, определяющую промежутки/границы интервалов, и добавил еще один столбец в исходную таблицу, где прописал бы формулой поиск по таблице интревалов (типа, Vlookup), тогда «Промежуточные итоги» надо настроить относительно значений этого дополнительного столбца. Или прописал бы в этом столбце нужные формулы. Если же мне не нужно было бы показывать этот столбец, я бы сделал его шириной 0.1 (не Hide), чтобы столбца не было видно глазом, но подписи к промежуточным итогам появлялись бы.
Или нужно писать программу, если «игра стоит свеч».
Evgeny Agafonov
Макрос
'в [b1] — сума чисел попадающих диапазон 30<х<180
'для проверки в столбец "А" набрать ряд чисел от 0 до 200
усл1 = ">30" '
усл2 = "<180"
Columns("A:A").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=усл1, Operator:=xlAnd, _
Criteria2:="усл2"
[b1] = Application.Sum(Selection)
Selection.AutoFilter
dedtolya
Вопрос
Как в макросе проверить, содержимое ячейки является текстом или числом?
Ответ
Sub CheckCellToNumber()
Dim MyVar As Variant
MyVar = ActiveCell.Value
If MyVar = "" Then
MsgBox "Ячейка пуста!"
Exit Sub
ElseIf IsNumeric(MyVar) Then
MsgBox "Это число!"»
Else
MsgBox "Это не число!"
End If
End Sub
Alex
Получился вот такой макрос:
Sub Число_Символ()
If IsNumeric(Cells(1, 1)) = True Then
Cells(1, 2) = "Число"
Else: Cells(1, 2) = "Символ"
End If
End Sub
Анатолий Киюк
Вопрос
Если вызовешь «формат ячеек», в графе «выравнивание» всегда автоматом стоит горизонтальное — по значению, вертикальное — по нижнему краю. Так уж получается, что мне всегда нужно «вертикальное по центру». Знает ли кто, где подправить параметры по умолчанию?
Ответ
Создайте книгу, содержащую листы, стандартный текст (например, заголовки страниц, подписи строк и столбцов), формулы, макросы, стили и другие элементы форматирования, которые должны быть в книгах, основанных на шаблоне. Сохранить файл как шаблон (*.xlt).
В окне Папка укажите папку, в которой должен быть сохранен шаблон. В Win2000 для создания шаблона по умолчанию выберите или папку XLStart, или дополнительную папку автозагрузки. Папка XLStart обычно находится в
C:ОСProfilesимя_пользователяApplication DataMicrosoftExcelXLStart
где ОС — папка операционной системы, например, Windows.
Для создания пользовательского шаблона выберите папку Шаблоны. В поле Имя файла введите Книга, чтобы создать стандартный шаблон книги. Для создания пользовательского шаблона введите любое допустимое имя файла.
Чтобы просматривать рисунок первой страницы шаблона в поле Просмотр диалогового окна Создать (меню Файл), в меню Файл выберите команду Свойства, нажмите вкладку Документ, а затем установите флажок Создать рисунок для предварительного просмотра.
Виталий, Valery Mezenin
Вопрос
Как в операторе цикла For задать проверку на пустую ячейку? То есть, когда i-тая ячейка окажется пустой, чтобы цикл завершался.
Ответ
Например, так:
For i = 1 To 10
Cells(i,1).Select
If ActiveCell.Value = "" Then Exit For
и так далее
Alex
Или так:
For i = 1 To 10
If Cells(i,1) = "" Then Exit For
******
For i = 1 To 20
If IsEmpty(Range("A" & i)) Then
MsgBox "Yes"
Exit For
End If
Next
dedtolya
Вопрос
Стоит следующая задача:
из внешнего истоника в определенную ячейку экселя поступает периодически изменяющееся значение. Надо в соседней ячейке формировать некое число (видимо, с помощью SpinButton) равное исходному значению плюс-минус некое количество шагов.
Например, исходное значение равно 4.016, тогда нажатие на стрелку вверх у SpinButton должно давать в заданной ячейке значения 4.017, 4.018 и т.д. Размер шага можно задать или в макросе или, что было бы элегантнее, брать из рядом расположенного текстового окна.
Ответ
Создать спин-кнопку SpinButton1 на листе и добавить следующие процедуры к листу.
Здесь подразумевается, что начальное значение в ячейке А1, шаг — В2, решение в В1.
Private Sub SpinButton1_SpinDown() ' уменьшает решение на шаг по нажатию спин-вниз
ActiveSheet.Cells(1, 2).Value = ActiveSheet.Cells(1, 2).Value —
ActiveSheet.Cells(2, 2).Value
End
Sub
Private
Sub
SpinButton1_SpinUp() ' добавляет шаг к решению по нажатию спин-вверх
ActiveSheet.Cells(1, 2).Value = ActiveSheet.Cells(1, 2).Value +
ActiveSheet.Cells(2, 2).Value
End
Sub
Private
Sub
Worksheet_Activate() ' переносится значение из исходной ячеки в решение при активации листа.
ActiveSheet.Cells(1, 2).Value = ActiveSheet.Cells(1, 1).Value
End Sub
Из конференции Expert_FAQ
Вопрос
Есть функция в файле *.xla. Как ее добавить в функции пользователя?
Ответ
Если эти функции находятся в разделе modules проектов открытого файла, то они сразу появляются как User defined functions в списке доступных функций. (Во всяком случае у меня так работает.)
Если необходимо, чтобы эти функции были доступны всегда, в любом другом документе, нужно перенести эти функции в personal.xls, который находится в XLSTART директории, правда, при этом, использовать эти функции в листе документа придется приблизительно так:
=personal.xls!userfunction(…)
Для этого надо открыть Ваш файл .xla, перейти в редактор VBA, найти нужные функции в modules и перенести их в modules файла personal.xls (Он обычно сам открывается при запуске Excel. В противном случае, может быть сгенерирован при записи макроса нажатий).
Евгений
Вопрос
Обычно в Exell заголовок строк имеет вид 1,2,3 и тд, а заголовок столбцов соответственно A,B,C и тд. А у меня почему то в обоих заголовках стоят цифры. Соответственно все ссылки на ячейки имеют какойто дикий вид. Как заголовки вернуть в нормальное состояние и отчего это происходит. У меня это произошло само. По крайней мере ни каких настроек я не менял.
Ответ
Сервис — Параметры — Общие — и убери галочку — стиль ссылок R1C1
Из конференции Expert_FAQ
Вопрос
Такая вот проблемка:
На листе Excel'a почти в каждой ячейке мне нужно добавлять комментарии. После этого каждый раз приходится лазить в «формат примечания» и ставить птичку что мол давай автоматический размер.
Нельзя ли задать стандартно для всех примечаний (новых или исправляя старые) автоматический размер ? Ну или пример макроса который мог бы создавать примечания с автоматическим размером допустим для 30 ячеек в ряд.
Ответ
Сделай один раз настройки, а потом правой кнопкой и скажи «по умолчанию для автофигур»
Вопрос
При использовании Microsoft Office в качестве базы данных и учета товаров, я столкнулся со многими трудностями. На некоторые из них я так и не смог найти ответ.
1. Автофильтр.
Требуется в накладной отфильтровать строки списка товара т.о., чтобы на экране остались только строки с определенным заполненным столбцом. Ставлю на этот столбец автофильтр. Все хорошо работает, но данная страница содержит в некоторых ячейках формулы, которые нельзя трогать! При защите листа автофильтр перестает работать! Что же делать? Можно ли снять защиту с автофильтра?
2. Копирование столбцов в строки и наоборот.
Excel — почти интеллектуальная система, она часто понимает, что от нее хочет пользователь. Однако это иногда раздражает. Необходимо скопировать ссылки на ячейки, например:
в ячейке A1: =B1
в ячейке A2: =B2
в ячейке A3: =B3
в строку 4 таким образом:
в ячейке A4: =B1, в ячейке B4: =B2, в ячейке C4: =B3.
Excel же воспринимает такое копирование как чередование СТОЛБЦОВ, а не СТРОК.
Как заставить Excel отменить или изменить эту функцию?
Ответ
Чтобы отключить защиту на автофильтр, нужно выполнить следующее:
1. Если лист защищен, отключить защиту листа — СЕРВИС\ЗАЩИТА\СНЯТЬ ЗАЩИТУ ЛИСТА…
2. Установить заново защиту листа — СЕРВИС\ЗАЩИТА\ЗАЩИТИТЬ ЛИСТ…
3. В вышедшем окне можно разрешить/запретить выполнение каких-либо операций с ячейками на этом листе, в том числе и использование автофильтра — просто поставь галочку :-) Все.
Чтобы скопировать строки в столбцы (или наоборот) выполни следующее:
1. Выдели ячейки, которые нужно скопировать, нажми ПРАВКА\КОПИРОВАТЬ
2. Выдели ячейки, в которые ты хочешь скопировать (или первую из них), потом ПРАВКА\СПЕЦИАЛЬНАЯ ВСТАВКА здесь выбери галочкой ТРАНСПОНИРОВАТЬ и жми ОК.
Из конференции Expert_FAQ
Вопрос
Есть файл нестандартного формата (например, с расширением ".bbb») и Excel-обработчик этих форматов (например, bbb_worker.xls). Как заставить Windows по двойному щелчку запускать этот обработчик, а обработчику получать имя файла для обработки?
Ответ
В принципе это можно сделать с помощью настроки параметров Папки в определнии типов файлов. Создайте там новый тип файлов (.bbb), создайте для него ассоциацию с программой Excel. В настройках запуска для команды по умолчанию (Например, Open или Открыть) внесите что-то типа этого:
"C:\Program Files\Microsoft Office\Office\EXCEL.EXE" "%1" "C:\My Documents\workers\bbb-work.xls"
Тогда при клике на файл с расширением bbb вместе с этим файлом откроется и файл обработчика. Если файл обработчика будет иметь процедуру обработки события открытия файла (Workbook_open), с открытием bbb файла автоматически будет запускаться и макрос записанный в коде обработки события Workbook_open. А там уже можете заставить программу делать все, что Вам захочется — проверить и получить все имена открытых файлов, выбрать нужный файл и обработать его.
(Прада всякий раз, когда Вы будете это делать, Excel будет спрашивать подтверждение о запуске файла с макросами, если это не отключено в настройках.)
Из конференции Expert_FAQ
Вопрос
Вот решил я в Personal.XLS вставить такой набор макросов, который должен запускаться в 2 часа пополудни. Но эта комбинация запускается не в 14-00, а каждый раз, когда я запускаю Эксель. Что я делаю неправильно?
Sub Auto_Open()
Call Time_Trapper
End Sub
Sub Time_Trapper()
Application.OnTime EarliestTime:=TimeValue("14:00"), _
Procedure:=Напомнить_Про_Обед, _
LatestTime:=TimeValue("14:30"), _
Schedule:=False
End Sub
Function Напомнить_Про_Обед()
…
…
End Function
Ответ
Имя процедуры попробуйте поместить в кавычки. Вот так работает без проблем:
Sub InTime()
Application.OnTime TimeValue("16:00:00"), "Hello"
End Sub
Function Hello()
MsgBox "!!!"
End Function
Alex
Вопрос
Версия программы: 2000
Можно ли, и если можно, то как защитить книгу Excel так, чтобы нельзя было изменять данные и главное — чтобы нельзя было даже в буфер обмена их копировать, только распечатывать. Защиту листа и книги пробовал. От буфера обмена не спасает. А надо, чтобы нельзя было скопировать данные, вставить в другую книгу и сделать вид что так и было.
Ответ
Я бы попробовал такой путь:
Добавил бы к коду книги такую процедуру, обрабатывающую событие изменения выделения (Selection), которая возвращала бы выделение в первую (или любую другую пустую) ячейку.
Тогда каждая попытка выделить какую-то другую область заканчивалась бы выделением только этой ячейки.
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Cells(1, 1).Select
End Sub
Понятно, что это ограничит и возможности для управлением печатью, но это можно решить отдельно форматированием или программно.
Из конференции Expert_FAQ
Вопрос
Как изменить цвет ячейки по условию, командой типа D3 color = green ?
Ответ
Если нужна сама эта возможность форматирования относительно значения или какого-либо условия, то это решается с помощью условного форматирования (Меню-Формат-Условное форматирование…). Если нужно, чтобы все работало именно от функции в самой ячейке, то мне кажется это невозможно по логике вещей из-за неопределенности точки вызова функции относительно обрабатываемого объекта. Хотя я не уверен. Может быть и есть какой-нибудь косвенный способ, но я его не знаю.
Из конференции Expert_FAQ
Вопрос
Результатом работы макроса является присвоение переменной значения, которое может оказаться довольно объемным текстом.
Можно ли этот текст автоматически экспортировать в word, другой текстовый редактор или хотя бы копировать в буфер обмена?
Ответ
Можно.
Начнем с последнего. Скопировать текстовую переменную VText в буфер обмена:
Set Fl = New DataObject
Fl.SetText Text:=VText
Fl.PutInClipboard
Эти три команды: создать новый объект, присвоить объекту значение переменной и переместить объект в буфер обмена.
С буфером обмена можно делать все, что угодно: например, вставить его в новый документ Excel и сохранить этот документ в нужном (например, в текстовом) формате. Но можно вставить буфер обмена и непосредственно в документ нужного текстовый редактора.
Чтобы вызвать редактор (допустим Word) нужно воспользоваться командой Shell:
Shell("C:\Program Files\Microsoft Office\Office\winword.exe", vbNormalFocus)
Чтобы вставить в документ редактора содержимое буфера обмена можно активизировать редактор и сделать вставку:
AppActivate "Microsoft Word", 1
SendKeys "+{INSERT}", 1
PS: Иногда подобные операции в «другой» программе требует небольшого времени ожидания на загрузку окна и т.д. Для задержки выполнения кода я использую небольшую процедуру:
Public Sub Idle(n As Single) Dim t As Single t = (Timer) If n < 0 Then MsgBox "OK?" Else While t + n > Timer Wend End If End Sub
Вызов этой процедуры задерживает выполнение кода макроса на указанное аргументом количество секунд или останавливает до нажатия на клавишу Ввода, если аргумент отрицательный.
Из конференции Expert_FAQ
Намного правильнее просто создать объект Word:
dim oWord as object
set oWord = CreateObject("word.application")
и сделать с ним все что нужно.
dimzon
Вопрос
При открытии существующего файла .xls, одновременно открывается пустая книга book1.xls. Некоторое время назад такого не было. В окне списка макросов ничего нет. При открытии файла из Excel пустышка не открывается. Как можно отключить открытие пустой книги?
Ответ
Была у меня такая проблема, тоже помучался. А решилась просто. Нашел этот файл и снес его. После этого больше подобного не было.. ;)
Из конференции Expert_FAQ
Нужно в каталоге, в котором установлен Excel, найти подкаталог XLStart. Любой файл (не обязательно формата xls), который там находится, автоматически открывается при открытии Excel. Так что в этом подкаталоге скорее всего и нужно искать пустую книгу book1.xls и удалить оттуда.
djonplayer
Вопрос
Понадобилось в Excel (vba) выполнить такое:
есть директория, в ней файлы формата xls, файлов переменное количество, называются они 1.xls, 2xls, 3xls…, n.xls
Структура файла такова:
два столбца с неограниченным количеством строк. Первый столбец — параметр, второй — значение. Необходимо программно определить и по возможности записать в файл или вывести на форму имя того файла, в котором какой-то конкретный параметр имеет наименьшее значение. Номера строк для одинаковых параметров в разных файлах не совпадают.
Понимаю, что долго сложно и мутарно, буду благодарен даже за помощь кусками, типа, к получит содержимое ячейки, как получить содержимое каталога, как…, ну вообщем поняли.
Ответ
Option Explicit
Function GetMinParamInFiles(Adir As String,
AParam As String) As String Dim iFile As Long,
resFile As String Dim MinValue As Variant,
' Ищем первый файл содержащий значение
iFile = 1
MinValue = Null
Do
resFile = Dir(Adir + CStr(iFile) + xlExt)
iFile = iFile + 1
If iFile >= 100 Then Exit Do
If Not resFile = "" Then
MinValue = GetMinParamInFile(Adir + resFile, AParam)
End If
Loop While (resFile = "") Or IsNull(MinValue)
' если нашли такой файл считем его за минимум и ищем дальше
If Not (resFile = "") And Not IsNull(MinValue) Then
MinValueFile = resFile
Do
resFile = Dir(Adir + CStr(iFile) + xlExt)
iFile = iFile + 1
If Not resFile = "" Then
curvalue = GetMinParamInFile(Adir + resFile, AParam)
If Not IsNull(curvalue) Then
If curvalue < MinValue Then
MinValue = curvalue
MinValueFile = resFile
End If
End If
End If
Loop Until (resFile = "")
End If
If IsNull(MinValue) Then
GetMinParamInFiles = ""
Else
GetMinParamInFiles = MinValueFile
End If
End Function
Function GetMinParamInFile(AFilename As String, AParam As String) As Variant
' Поиск значения заданного параметра в заданном файле
' Если такого параметра нет возвращается Null
Dim wb As Workbook
Set wb = Workbooks.Open(AFilename)
Dim ws As Worksheet
' Здесь я принимаю, что параметры хранятся на активном листе
Set ws = wb.ActiveSheet
Dim iRow As Long
iRow = 1
Dim Pname As String, Pvalue As Variant
Pvalue = Null
Pname = Trim(ws.Cells(iRow, 1).Value)
Do While Not Pname = ""
If Pname = AParam Then
Pvalue = Trim(ws.Cells(iRow, 2).Value)
Exit Do ' считаю что параметр в файле встречается всего один раз
End If
iRow = iRow + 1
Pname = Trim(ws.Cells(iRow, 1).Value)
Loop
wb.Close
GetMinParamInFile = Pvalue
End Function
Sub test()
MsgBox GetMinParamInFiles("c:\мои документы\", "p4")
End Sub
Из конференции Expert_FAQ
Вопрос
При открытии книги спрашивает, надо ли обновить связи с другой книгой. А таких связей в этой книге быть не должно. Как мне найти ячейки, где прописаны связи с другими книгами и потом удалить их?
Ответ
1. Нужно избавиться от ссылок в старый файл в формулах на всех листах. Чтобы найти такие ссылки можно выделить все листы в новом файле и сделать поиск такой строки — "=[" (без кавычек). Удалить часть найденной строки формулы в квадратных скобках и вместе со скобками — это будет имя другого файла и путь к нему (можно произвести произвести замену во всей книге на пусто).
2. Нужно избавиться от именованных областей с ссылками на другой файл. Меню — Вставить — Имя — Определить… — здесь удалить или переопределить именнованные области со ссылками на внешний файл.
Из конференции Expert_FAQ
Первым делом войди в меню «Правка» и выбери пункт «Связи», там указываются все файлы, с которыми связана твоя книга (находится в первой колонке — «Исходный файл»).
Например «C:\…\Вексель.xls» и «Z:\…\Номенклатура.xls». Потом начинаешь искать эти файлы (названия этих файлов), заключенные в квадратные скобки. В данном случае — [Вексель.xls] и [Номенклатура.xls]. Должны быть сняты флажки (в диалоге поиска) — «Учитывать регистр» и «Ячейка целиком» и установлена «Область поиска» на «Формулы».
Вадим
Вопрос
При открытии книги появляется окно с предложением обновить связи с другой книгой. Эти связи появились случайно (вследствие каких-то моих бестолковых действий).
Как найти ячейки, содержащие эти связи, чтобы убрать их. Книга большая, простое просматривание каждой ячейкм займет бездну времени.
Ответ
Первым делом войди в меню «Правка» и выбери пункт «Связи», затем перепиши все файлы на которые ссылается данная книга (находится в первой колонке — «Исходный файл»), например «C:\…\Вексель.xls» и «Z:\…\Номенклатура.xls».
Потом начинаешь искать эти файлы (названия этих файлов), заключенные в квадратные скобки. В данном случае [Вексель.xls], и [Номенклатура.xls].
Должны быть сняты флажки (в диалоге поиска) — «Учитывать регистр» и «Ячейка целиком» и установлена «Область поиска» на «Формулы». Я понимаю, что последний совет из разряда «Деточка, если не дышать — то помрешь» ;-), но все же — на всякий случай.
Вадим
Вопрос
Как написать макрос, который бы обрабатывался при изменении данных в листе? Другими словами, изменил я ячейку А1, а ячейка В2 автоматически пересчиталась, но данные пересчитывал бы мой макрос.
Ответ
Войдите в редактор макросов (Alt-F11). Найдите в дереве активных проектов свой файл.
В этом проекте найдите лист, куда Вы хотите вставить макрос обрабоки события. Выделите этот пункт в дереве. Нажмите F7 — окроется окно ввода программного кода, связанного с этим листом.
Внесите в это окно пример ниже:
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Cells(2, 1).Value = ActiveSheet.Cells(1, 1).Value *
ActiveSheet.Cells(1, 2).Value
End Sub
После чего при каждом изменении любой ячейки листа в ячейку A2 будет заново вноситься значение произведения A1 и B1. Далее по нуждам, измените обработку этого события или внесите новые программы на обработку любого другого события, возможного в листе или в книге.
Из конференции Expert_FAQ
Вопрос
Версия программы: Office XP
При работе с ячейками и в Excele можно использовать множество встроенных функций типа МАХ, SUM etc.
А как написать пользовательскую функцию? Я не знаком с VBA, поэтому хотелось бы в живую увидеть хотя бы один готовый пример пользовательской функции и понять, как их создавать (собственно нужен макрос, который по заданным точкам (=значениям ячеек) смоделирует математическую функцию, т.е. попросту сделает из точек ломаную функцию, соединив их, и сможет выдать значение этой функции для конкретного агрумента).
Ответ
1. Создание пользовательской функции.
Открываем редактор Visual Basic
(Service — Macros — Visual Basic Editor, или [Alt]-[F11] с клавиатуры).
Слева, в окошке с деревом проекта, щелкаем правой кнопкой мыши по названию книги, выбираем Insert — Module, получаем Module1. По нему двойной левой, справа появляется его содержимое (пока пустое). Там набираем примерно такой код:
Public Function TestFormula(a As Range)
Dim z as Long z = 0 For i = 1
To a.Rows.Count z = z + a.Cells(i, 1).Value Next
TestFormula = z End Function
Подробнее:
TestFormula — имя формулы, произвольное.
a — переменная-аргумент, в данном случае диапазон, т.е. функции можно будет подсунуть «A1:B5».
Если нужна будет только одна ячейка, то пишем «a As Variant»
Дальше идет короткий пример программы:
"Dim <имя> As <тип>» — объявление переменной (кстати, не обязательно, можно «на лету»)
"For <от> to <до>" — простейший цикл. Здесь a.Rows.Count — количество строк в аргументе-диапазоне. Соответственно a.Columns.Count — количество колонок, и.т.д.
"z = z + " — это, я думаю, и так понятно.
"a.Cells(i,1).Value", буквальный перевод такой:
В объекте «a» (который у нас типа «диапазон», в англ. варианте Range) взять объект «Cells» (который тоже, кстати, Range, т.е. массив ячеек), там взять объект (т.е. ячейку) с индексом i (строка), 1 (столбец), и у этого объекта взять свойство Value (т.е. значение).
"TestFormula = z" — самая важная в функции строка, которая устанавливает,какое значение она возвращает. Слева должно стоять имя функции, как в заголовке. Справа — что нам нужно.
Таким образом, получаем функцию, суммирующую все ячейки в первой столбце указанного диапазона.
На самом деле, многое еще надо доработать, например, что делать, если текст попадется, так как этот пример выдаст ошибку.
Если нам нужны еще функции, то их можно создать здесь же.
Т.е. еще раз пишем
Public Function <имя>(<список аргументов>) As <возвращаемый тип>
<код функции>
End Function
и.т.д.)
2. Применение всего этого.
Допустим, в ячейках A1…A10 находятся числа. Их сумму нужно поместить в ячейку B1.
Тогда в ней пишем:
=TestFormula(A1:A10)
В списке функций (там, где всякие MAX и т.п.) наша будет находиться в разделе «Другие функции», подразделе «Определенные пользователем», который самый последний. У меня русская версия, в английской, вероятно, будет «Other…» — «User defined».
3. По смыслу задачи. Возможно, помогут функции TEND или FORECAST. Ломаная, по-моему, Excel строить не умеет. Есть линия тренда типа «линейная фильтрация», но в функциях я ее не нашел.
Или же, придется строить многочлен Лагранжа, но это уже отдельная тема.
Из конференции Expert_FAQ
Вопрос
Как начать выполнение макроса сразу после открытия xls-файла?
Ответ
Sub Workbook_Open()
MsgBox «Макрос запущен»
End Sub
Этот макрос автоматически запускается при открытии Excel-документа, в который он встроен. Для этого он должен быть помещен в модуль ThisWorkbook, который есть в любом документе.
Из конференции Expert_FAQ
То же самое произойдет, если создать макрос с именем Auto_Load() и поместить ее в любой модуль книги.
ag
Вопрос
Вопрос 1
Есть ли такой хоткей, чтобы начать редактировать ячейку. Ситуация: в ячейке есть текст, мне нужно его подправить. Для этого я подвожу к ячейке курсор мыши и даблкликаю. Хочу обойтись без мыши.
Вопрос 2
Как отключить фишку, когда только начал вбивать в ячейку данные, и вдруг выяснил, что десятью символами ранее допустил ошибку. Нажал стрелку «влево», но вместо того, чтобы сдвинулся курсор, завершается ввод и становится текущей ячейка слева от только что редактируемой. Приходится делать действия из вопроса 1 :(
Ответ
На оба вопроса — один и тот же ответ:
Нажатие клавиши F2 фиксирует состояние редактирования ячейки, тогда нажатия клавиш вправо-влево по строке внутри ячеки перемещает курсор внутри ячейки. Кстати эта клавиша удобно работает еще в некоторых других местах Офиса и Окон. Например, очень удобно перейти к переименованию выделенного имени файла в Проводнике.
Из конференции Expert_FAQ
Вопрос
Подскажите, пожалуйста, какой оператор в VB может определить содержиться ли в ячейке (текстовом значении) определенный набор символов?
Ответ
Like Operator Example This example uses the Like operator to compare a string to a pattern. Dim MyCheck MyCheck = "aBBBa"
Like "a*a"
' Returns True. MyCheck = "F"
Like "[A-Z]"
' Returns True. MyCheck = "F"
Like "[!A-Z]"
' Returns False. MyCheck = "a2a"
Like "a#a"
' Returns True. MyCheck = "aM5b"
Like "a[L-P]#[!c-e]"
' Returns True. MyCheck = "BAT123khg"
Like "B?T*"
' Returns True. MyCheck = "CAT123khg"
Like "B?T*"
' Returns False.
Иногда, бывает неплохо в хелп залесть.
Из конференции Expert_FAQ
Вопрос
Есть две даты. Каждой дате соответствует определенное время. Информация «дата»-«время» заносится в разные столбцы. Как определить интервал времени между двумя датами?
Ответ
Если из большей даты вычесть меньшую, то мы получим количество дней между этими датами.
Дальше если у конечной даты время больше времени начальной даты, то полученную сумму дней умножаем на 24 часа — получим количество прошедших суток в часах + разница между конечным и начальным временем. Если начальное время больше конечного (т.е. прошли неполные сутки), то количество дней минус 1 умножаем на 24 + (24 — (начальное время — конечное время)). В итоге получаем количество часов и минут, прошедшее между двумя датами.
Из конференции Expert_FAQ
Для определения интервала между датами во встроенном языке есть функция
DateDiff(interval, date1, date2[, firstdayofweek[, firstweekofyear]])
Синтаксис функции DateDiff содержит следующие именованные аргументы:
interval Обязательный. Строковое выражение, указывающее тип временного интервала, который следует использовать при вычислении разности между датами date1 и date2. date1, date2 Обязательные. Значения типа Variant (Date). Две даты, разность между которыми следует вычислить.
firstdayofweek Необязательный. Константа, указывающая первый день недели. Если этот аргумент опущен, считается, что неделя начинается с воскресенья.
firstweekofyear Необязательный. Константа, указывающая первую неделю года. Если этот аргумент опущен, первой неделей считается неделя, содержащая 1 января.
Однако, она имеет, мягко говоря, особенность (на самом деле это очередной «ляп» Microsoft), а именно:
Это из Help:
«При сравнении дат 31 декабря и 1 января следующего года функция DateDiff для интервала типа год («yyyy») возвращает значение 1, хотя разница между датами составляет всего один день.»
Поэтому, (например, для определения полных лет между двумя датами) я использую собственную маленькую функцию:
//---------------------------------
Public Function ПолныхЛет (ПерваяДата As Date, ВтораяДата As Date) As Integer
Dim ПМ, ВМ, ПД, ВД, Годы As Integer
If (ВтораяДата = Null) Then
ВтораяДата = Now
End If
Годы = DateDiff(«yyyy», ПерваяДата, ВтораяДата)
ПМ = Month(ПерваяДата)
ВМ = Month(ВтораяДата)
If ВМ < ПМ Then
ПолныхЛет = Годы — 1
ElseIf ВМ > ПМ Then
ПолныхЛет = Годы
ElseIf ВМ = ПМ Then
ПД = Day(ПерваяДата)
ВД = Day(ВтораяДата)
If ВД <= ПД Then
ПолныхЛет = Годы — 1
Else
ПолныхЛет = Годы
End If
End If
End Function
Сергей Нелюбов
Вопрос
У меня есть таблица с данными в Excele. В одной из колонок — артикулы товара. Хотелось бы узнать, как в Excele можно осуществить поиск номеров артикулов в колонке таким образом, чтобы номер артикула вводить в одной определJнной ячейке (например А1), а найденная ячейка в столбце с таким же номером артикула как в А1 выделялась, либо туда переходил бы курсор.
Ответ
Заходим в Excel, жмем ALT+F11 — попадаем в VBA. Далее жмем CTRL+R — открывается окно проекта. Кликаем на Лист1 и вставляем туда следующий код:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target = [A1] And IsEmpty([A1]) = False Then _
[A2:A65535].Find([A1], [A2], xlValues, xlWhole, xlByColumns, , True).Select
End Sub
Диапазон ячеек и параметры поиска можно поменять при надобности. Если кратко, то
Find([A1], [A2], xlValues, xlWhole, xlByColumns, , True)
Поиск ячейки ([A1] — что именно ищем (значение ячейки А1), [A2] — после какой ячейки начинать поиск, xlValues — ищем значения, xlWhole — полное или частичное соответствие (xlPart — частичное. Если поставить, то по некоторым значениям найдет первую подход
ящую ячейку. Удобно, если много числовых значений в искомой ячейке), xlByColumns — порядок поиска (по столбцам — xlByColumns, по строкам — xlByRows), пропущенный параметр, True — различать или нет регистр символов поиска (допустим заглавная буква или не
т.False — наоборот).
Из конференции Expert_FAQ
Вопрос
Написал макрос для Excel. По ходу работы нужно считать данные из другой рабочей книги. Но в этой книге есть свой макрос, который начинает работать сразу при открытии книги. Причем как только он отработал, сразу закрывает Excel. Получается так.
Начинает работать макрос первой рабочей книги, потом он открывает вторую рабочую книгу, там начинает работать его макрос. Он перехватывает управление, добросовестно отрабатывает до конца и закрывает Excel. Без возврата в первый макрос.
Вопрос. Какой код нужно написать в первом макросе, чтобы открыть вторую рабочую книгу без активации его макроса?
Я открываю вторую рабочую книгу такой процедурой:
Sub Temper_Open()
Workbooks.Open ("c:\xls\Temper.xls"), ReadOnly:=True
Workbooks("c:\xls\Temper.xls").RunAutoMacros xlAutoDeactivate
End Sub
В моей процедуре деактивировать уже поздно, т.к. макрос уже отработал и требует сохранить изменения в файлах.
Ответ
Можно предложить такой вариант.
Во второй книге, в автозапускающемся макросе вставить проверку, какого нибудь параметра и на основе его значения делать вывод продолжать ли выполнение этого мароса или нет.
В качестве такого параметра может быть какая-нибудь public переменная в первом документе, значение которой можно проверить во втором документе.
Другой вариант, использовать какой-нибудь текстовый файл, в который записывать какое-нибудь значение, а во втором документе в автозапускающемся макросе проверять наличие или отсутствие этого значения и на основании этого делать выводы, продолжать или нет работу автозапусающегося макроса.
Из конференции Expert_FAQ
Вопрос
Вопрос по Visual Basic'у для Excel'а:
Есть ли команда, открывающая окно Сервис \ Защита \ Защитить лист (или Снять защиту листа)?
Ответ
Application.Dialogs(xlDialogProtectDocument).Show
В принципе, после .Show можно было бы перечислить значения именнованных аргументов, чтобы при открытии диалога поля были бы предустановлены в нужные значения, но у меня это почему-то не заработало (пока не знаю почему). См. Помощь для Dialogs.
Из конференции Expert_FAQ
Вопрос
Как сделать так, чтобы на экране отображалась форма и в ней отображалось над какой ячейкой находится мышка.
Ответ
Зайди в VBA и помести этот код прямо в Лист1 (если нужно, чтобы в других листах было то же самое, то скопируй этот код во все остальные листы или создай класс, чтобы не повторяться):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox ActiveCell.Address
End Sub
Из конференции Expert_FAQ
Вопрос
У меня возникла необходимость в Excel выбирать из списка значения по столбцам и передавать их в другие ячейки. Использую панель «Формы» и значок «выбрать из списка» Но в указанные ячейки передаются только номера записи по порядку. Возможно нужно написать какой-то макрос?
Ответ
Можно зайти в редактор VB, создать форму, на нее положить выпадающий список (или простой список — как понравится), назначить ему RowSourse тот диапазон, из которого нужно выбирать, а ControlSourse — ту ячейку, куда надо помещать выбранное значение. Если надо сразу из нескольких столбцов выбирать, то помести несколько списков… Или создай несколько форм — как тебе выгоднее. Затем напиши макрос, в котором будет строчка [ИмяФормы].Show
и пользуйся им для запуска формы.
Анатолий
Пусть:
диапазон для списка — «А1:А10»
ячейка, куда помещается результат из списка — B1
Тогда напиши в B2 формулу:
=ИНДЕКС(A1:A10;B1)
Если другие ячейки — это столбец в таблице, воспользуйся:
Данные — Проверка — Проверка вводимых значений — Тип данных: Список
*******
А если нужен такой вариант:
На листе1 ячейка, в которую подставляются значения из списка, а сам список на другом листе, Для этого нужно списку (диапазон ячеек) на другом листе присвоить имя (например, «данные»), а затем Данные — Проверка — Проверка вводимых значений — Тип данных: Список. Только вместо запрашиваемого диапазона в ДО, ввести имя списка («данные»).
MaxKP
Вопрос
Создал достаточно большой список своих фильмов в Excel, теперь хочу перенести все данные в Access, никак не получается скопировать содержимое примечаний, (там у меня подробности о фильме — жанр, содержание etc.). Как это можно было бы сделать, и можно ли это автоматизировать?
Ответ
Думаю, удобней будет сначала вынести все комментарии к ячейкам в свободные ячейки, а уж потом импортировать таблицу в Access
Public Sub AllCommentsToCells() Dim cb, ce, rb, re, nc, i, j As Integer
'определяем границы области обработки
cb = Selection.Column ce = Selection.Column + Selection.Columns.Count — 1 rb = Selection.Row re = Selection.Row + Selection.Rows.Count — 1
'номер последнего использованного столбца
Set cs = ActiveWorkbook.ActiveSheet nc = cs.UsedRange.Columns.Count On Error Resume Next
'перебор всех ячеек с выносом комментариев
'в пустые ячейки по строкам
For i = cb To ce For j = rb To re cs.Cells(j, nc + i — (ce — cb)).Value = _ cs.Cells(j, i).Comment.Text Next j Next i
End
Sub
Этот макрос вынесет все содержания комментариев в выделенной области листа в неиспользованные ячейки справа от исходной таблицы по соответствующим строкам и столбцам. Думаю будет не сложно потом получившееся подготовить к импорту в Access.
Из конференции Expert_FAQ
Вопрос
Существует столбец в таблице, в который заносятся фамилии работников. Необходимо, чтобы в конце столбца было указано количество занесенных фамилий.
Ответ
Вариант раз:
=СЧЕТЗНАЧ(A1:A5)-СЧЕТЕСЛИ(A1:A5;0)
Подсчитываем общее количество строк и вычитаем нулевые.
Вариант два: добавляем еще один столбец с формулой
=ЕСЛИ(ЕТЕКСТ(A1);1;0)
Она выдаст 1, если в ячейке А1 — фамилия. Остается просуммировать все значения ячеек с данной формулой.
Анатолий Киюк
Вопрос
Какую функцию Excel использовать ?
Есть несколько ячеек, в них записаны некие значения, некоторые повторяются.
Есть интервал в который попадет некоторое количество значений.
Какой функцией подсчитать количество попавших в интервал значений ?
Есть СЧЕТЕСЛИ(<диапазон ячеек со значениями>;<условие для подсчета>), но он не работает с ячейками. Т.е. если указать в условии "<>2" , то функция сосчитает количество значений неравных 2, но если указать "<>B2" (а B2=2) , то функция не заработает, т.к. будет искать строки, у которых значение является _строкой_ "<>B2". Как быть ?
Ответ
Думаю, что одной функцией тут не обойтись. Я бы сделал предварительное вычисление в отдельном столбце на предмет попадания значения в вычисляемый диапазон и уж тогда считал не сами значения, а результаты «попаданий». Например, в С1 и С2 находятся значения верхней и нижней границы диапазона. В столбце А — значения, в столбце В на против каждого значения формула типа:
=ЕСЛИ(И(A1>=C$1;A1<=C$2);1;0)
Тогда можно использовать СУМЕСЛИ или просто СУММА для подсчета единиц в столбце В.
Другой путь, возможно, можно было бы решить в сводной таблице с вычисляемым полем. Правда решение подобное, как уже описано выше, но, по моему разумению, в сводной таблице можно было бы сосчитать не один, но несколько диапазонов сразу. Но это уже если задача может встать шире, чем описано.
Из конференции Expert_FAQ
В критерии "<>B2" вместо B2 надо подставить текстовое представление значения этой ячейки. Попробуйте в качестве критерия использовать "СЦЕПИТЬ("<>";ТЕКСТ(B2;"####0"))" (формат подкорректируйте под свои значения).
cat126
Вопрос
Мне нужно посчитать сумму ячеек формата «время» в столбце. Сумма получается какая-то неправильная — дробное число.
Ответ
Если у ячеек формат «время», то сумма будет считаться правильно, если у ячейки с суммой тоже установить формат «время».
Arkady Bernadsky
Все замечательно, но есть одно НО! Если эта сумма превышает 24 часа, то результат такого суммирования будет «сумма-24». Решение вопроса — установка формата времени в таком виде:
[hh]:mm
Вопрос
Как поставить книгу на пароль. т.е. при открытии файла счет-фактура.xls требовалось бы ввести пароль.
Ответ
Вырезка из хелпа к Excel
* Выберите команду "Сохранить как" в меню "Файл".
* Выберите в меню "Сервис" команду "Общие параметры". (прим. "Сервис" выбирается в том окне где предлагают сохранить
* "Сохранить как"(правый верхний угол), а не в главном меню Excel)
* Введите пароль в поле "Пароль" для открытия файла и нажмите кнопку OK.
* Введите пароль еще раз в поле "Введите пароль" еще раз и нажмите кнопку OK.
* Нажмите кнопку Сохранить.
Из конференции Expert_FAQ
Вопрос
Как при открытии файла запустить сразу же макрос, или форму?
Ответ
Нужно войти в Редактор Visual Basic (Alt + F11), найти в окне проекта (Project Explorer, см. в меню View-Вид) VBAProject (PERSONAL.XLS), а в нем Microsoft Excel Objects — This Workbook (открыть окно кода двойным щелчком). Далее создать процедуру Workbook_Open() и в ней записать имя вызываемого макроса. Если же нужно вызывать форму, то пишите вместо имени макроса следующее:
Load MyForm
MyForm.Show
(где MyForm — имя Вашей формы)
Alex
Можно сделать и так:
Sub auto_open()
…
End Sub
Владимир
Вопрос
В Excel имеется длинная таблица. Как програмно в VBA определить конец страницы ?
Ответ
Чтобы определить количество строк в таблице можно сделать так, если таблица начинается с первой строки в первом столбце листа:
ActiveSheet.Cells(1,1).Select
Selection.CurrentRegion.Select
R = Selection.Rows.Count
C = Selection.Columns.Count
В R будет количество строк в таблице, если таблица не имеет разрывов, т.е. не имеет полностью пустых строк.
В C — количество столбцов в таблице, если нет полностью пустых столбцов (впрочем, если заголовки есть во всех столбцах, то это уже решает все).
Или можно использовать функцию counta, которая эквивалентна функции вроде бы СЧЕТЗН в русской версии (не уверен, у меня английская):
R = WorksheetFunction.CountA(Range(«A1:A65536»)) 'сосчитает количество непустых значений в первом столбце листа
C = WorksheetFunction.CountA(Range(«A1:IV1»)) 'сосчитает количество непустых значений в первой строке листа
Правда в данном случае, нужно быть уверенным, что на листе нет других таблиц.
Если таблица имеет сложную структуру и есть несмежные области данных, то скорее всего придется делать цикл и перебирать все строки пока не обнаружится конец таблицы.
Из конференции Expert_FAQ
Вопрос
Нужно из двух(многих) файлов(прайсов) произвести выборку позиций и объединить их в один файл:
1. Названия позиций повторяются и нужно если позиция была в первом файле из всех остальных брать только цены и подставлять в соответствующую строку.
2. Цены и упаковку надо подставлять в разные колонки(для каждого прайса своя) но строки с названиями позиций не должны повторяться.
В общем:
в новом листе 1-я колонка название товара
2-я колонка упаковка
3-я,н-я колонка цена на товар по каждой фирме
Понимаю что надо писать макрос, или как-то играться с аксессом(БД), но я как бы чайник, поэтому, если у кого есть готовое решение или какие идеи — милости просим!
Ответ
Если, конечно, я правильно понял задачу…
Public Sub AllToOne()
Dim i, j, cc, nr As Integer 'создать новую книгу с одним листом
Set nw = Workbooks.Add(xlWBATWorksheet)
Set ns = nw.ActiveSheet
Set af = Application.WorksheetFunction On Error Resume Next c = 2 g = 1 'цикл по всем книгам кроме новой
For i = 1 To Workbooks.Count — 1 If Workbooks(i)
.Name <> "Personal.xls" Then 'цикл по всем листам текущей книги
For j = 1 To Workbooks(i)
.Worksheets.Count Set cs = Workbooks(i)
.Worksheets(j) 'перенести заголовки текущ.листа в новую книгу
If c = 2 Then ns.Cells(1, 1) = cs.Cells(1, 1)
ns.Cells(1, 2) = cs.Cells(1, 2)
End If c = c + 1 ns.Cells(1, c) = Workbooks(i)
.Name & ":" _ & Workbooks(i).Worksheets(j)
.Name & _ ":" & cs.Cells(1, 3) 'цикл по всем строкам текущ.листа nr = cs.Cells(1, 1)
.CurrentRegion.Rows.Count For n = 2 To nr 'перенести данные строки в новую книгу:
Err.Clear m = af.Match(cs.Cells(n, 1).Value, _ ns.Range("A:A"), False) 'если нет таких данных, то в новую строку
If Err.Number > 0 Then g = g + 1 ns.Cells(g, 1)
.Value = cs.Cells(n, 1)
.Value ns.Cells(g, 2)
.Value = cs.Cells(n, 2)
.Value ns.Cells(g, c)
.Value = cs.Cells(n, 3)
.Value Else 'если есть такие данные, то цену в ту же строку
ns.Cells(m, c)
.Value = cs.Cells(n, 3)
.Value End If 'конец цикла по строкам Next n 'конец цикла по листам Next j 'конец цикла по книгам
End If Next i
End
Sub
Программа просмотрит все листы всех открытых книг. Т.е. перед запуском макроса нужно открыть все книги с прайсами.
Из конференции Expert_FAQ
Вопрос
Возможно ли, если вся таблица уже заполнена, произвести деление всех чисел введенных в форму (за исключением тех, которые рассчитаны по формулам) на какое-то одно число.
Ответ
Записываете нужное число в свободной ячейке, копируете содержимое ячейки в буфер.
После этого выделяете нужные для пересчета ячейки, при этом можно производить выделение не только обычными блоками, но и множественные, исключая ячейки с формулами. Через контекстное меню (правой кнопкой мыши) выбираете «Специальная вставка…», далее операцию «разделить» и ОК.
Из конференции Expert_FAQ
Вопрос
Как проставить в Гистограмме значение к каждой линии гистограммы по оси Х ?
Указываю «Диапазон по оси Х» на вкладке «Ряды», но отображается только одно из значений диапазона, а линий гистограммы 8 и под каждой хочется учидеть числовое значение.
Ответ
Щелкаешь на оси правой кнопкой мыши, выбираешь формат оси. В открывшемся окне, переходишь на вкладку Шкала и во все текстовые поля ставишь 1, тогда все столбцы пронумеруются по порядку ЗЫ: я надею правильно тебя понял, если нет пришли файл с подробным описанием чего тебе нужно.
Из конференции Expert_FAQ
Вопрос
Что, кроме явных ссылок в формулах и определенных в других файлах имен (и то, и другое я уже удалил), может являться причиной наличия связи книги с другими файлами? Как эти причины выяснить, и как от них избавиться?
Мне приходится сейчас выправлять достаточно некачественную работу других людей и необходимо быть полностью увененным, что указаываемые в \Правка\Связи… ссылки не влияют на вычисления.
Ответ
Связи с другой книгой удаляются так: меню Правка-Связи… Открывается окно, в нем есть кнопки Разорвать связь и Запрос на обновление связей, если хочешь чтоб просто не спрашивало об обновлении жмешь Запрос на обновление связей, если хочешь полностью исключить связи жмешь Разорвать связь, при этом связаные ссылки принимают значения отображаемых ими в данный момент значений.
Из конференции Expert_FAQ
Вопрос
Как распознать в процедуре какой объект помечен: строка, столбец или ячейка (или их группы)?
Ответ
Надо анализировать свойство count. Пример из справки:
Worksheets("Sheet1")
.Activate areaCount = Selection.Areas.
Count If areaCount <= 1 Then MsgBox "The selection contains " & _ Selection.Columns.Count & " columns." Else For i = 1
To areaCount MsgBox "Area " & i & " of the selection contains " & _ Selection.Areas(i)
.Columns.Count & " columns." Next i
End If
Из конференции Expert_FAQ
Вопрос
Первая ячейка: наличие на начало года (C5)
Вторая ячейка: прибавление за месяц (D5)
Третья ячейка: закуплено за месяц (E5)
Четвертая ячейка: выбыло за месяц (F5)
Нужно узнать выбытие за месяц в процентах, ячейка (G5)
В ячейке G5 написано следующее: =F5/(C5+D5+E5)
Все нормально пока все ячейки заполняются, но если к примеру за месяц ничего не прибавлено (D5) выдается ошибка #ДЕЛ/0! (Деление на ноль). Как решить эту проблему с ячейками?
Ответ
Вставь условие ЕСЛИ
т.е. ЕСЛИ C5+D5+E5 = 0 тогда не считать, иначе F5/(C5+D5+E5)
Роман
Вопрос
Как с помощью макроса задать координаты расположения диаграммы на листе?
Ответ
ActiveSheet.Shapes("Диагр. 1").Left
ActiveSheet.Shapes("Диагр. 1").top
Из конференции Expert_FAQ
Вопрос
Мне нужно сделать в ячейке текст из нескольких строк. Но при нажатии перевода строки [Enter] происходит переход на следующую ячейку. Каким образом можно вводить в ячейку многострочный текст?
Ответ
Жми [ENTER], удерживая клавишу [Alt].
Из конференции Expert_FAQ
Вопрос
Подскажите, как сделать в ячейке формат даты ГГ-ММ-ДД. По умолчанию ставится наоборот ДД-ММ-ГГ.
И подскажите, как написать макрос, чтобы при нажатии кнопки, добавлялся один месяц, год. Желательно не в одной ячейке, а в диапазоне, например в столбец.
Ответ
Формат ячейки — Число — Дополнительный формат, а там прописываешь шаблон в виде ГГ-ММ-ДД.
Из конференции Expert_FAQ
ЭТО МАКРОС:
Sub AddPeriod(strRan As String, intPlus As Integer, Optional bMon As Boolean)
'Добавление указанного периода ко всем ячейкам указанного диапазона
'strRan — адрес (напр., "A1:C3"), intPlus — сколько прибавлять,
'bMon — логический параметр. Если не указывать, добавляется указанное в intPlus число лет,
'если задать как True — месяцев
Dim rng As Range, rnc As Range
Set rng = Range(strRan)
For Each rnc In rng.Cells
If IsDate(rnc.Value) Then
If bMon Then
rnc.Value = DateAdd("m", intPlus, rnc.Value)
Else
rnc.Value = DateAdd("yyyy", intPlus, rnc.Value)
End If
End If
Next
End Sub
А ЭТО ПРИМЕР ЕГО ВЫЗОВА:
Sub AddTwoMonths()
'Добавляет 2 месяца к каждой дате, найденной в диапазоне "A1:G7"
AddPeriod "A1:G7", 2, True
End Sub
Leshek
Вопрос
Дана исходная таблица с колонками:
— наименование товара
— кол-во проданного товара (целое число)
— период (целое число).
Необходимо получить таблицу, где колонкам бы соответствовали периоды, а строкам номера товаров. На пересечениях — кол-ва проданных. Не в каждом периоде присутствуют все товары (где нету — подразумеваем 0). Как заполнить таблицу? Офис английский.
Ответ
Это классическая задача для решения в сводной таблице. Причем ни перечень товаров, ни перечень периодов отдельно не нужны. Все будет вычисляться автоматически. Хотя, если в списке нет значений для некоторых возможных периодов, которые обязательно должны отразиться в таблице вне зависимости от существования данных для этого периода, я бы внес в список хотя бы по одному нулевому («фальшивому») значению для каждого периода, который обязательно должен появиться в сводной таблице. Меню — Data — Pivot Table…
Евгений
Вопрос
Версия программы: Excel 2000 Rus, Excel XP Rus
Есть книга Екселя из многих листов, на каждом листе список : Фамилия, Имя, Отчество, Начисления и проч. По всем листам формируется сводная ведомость по начислениям. Людей приходится двигать (уволенные, вновь принятые). Есть ли в Екселе функция, или как написать макрос, чтобы контролировать, что «Иванов», «Петр», «Сергеевич» на всех листах стоит в одних и тех же адресах, например А3, В3, С3? И если это не так, чтобы выдавалось сообщение.
Ответ
Чтобы не связываться с макросами, можно установить зависимости ячеек с именами на втором и последующих листах от аналогичных ячеек на первом листе.
Например, на первом листе в ячейке A1 фамилия «Петров», а на втором листе формула "=Лист1!A1".
Тогда, при удалении на первом листе какой-либо строки, на втором выскочит в ячейке сообщение об ошибке, типа "######", а связи сохраняться. Теперь такие строки можно удалять.
Способ простой, конечно, для больших таблиц не годится… Но так ли часто Вы увольняете людей?
Из конференции Expert_FAQ
Вопрос
В Excel-97 есть таблица — по строкам наименования, по столбам числа. Требуется сделать — по строкам числа, по столбам наименования. Как это можно перевернуть?
Ответ
Это делается с помощью транспозиции (Tranpose) при специлаьной вставке — не уверен, как на русском это точно называется.
Нужно:
— скопировать нужную таблицу полностью в буфер клавиатуры.
— установить курсор в нужное место, где должна находиться новая версия преобразованной таблицы
— Правка-Специальная вставка-Транспозиция (Edit-Paste Special-Transpose)-OK.
Из конференции Expert_FAQ
Вопрос
Как в MS Excel сделать так, чтобы данные вида "1234" или "1234,00р." переводились в строку вида «Одна тысяча двести тридцать четыре руб. 00 коп.»?
Ответ
Непосредственно в Excel такой возможности нет, но существует достаточно много сторонних разработок, например здесь:
www.microsoft.ru/offext
Мне понравилась эта: www.fido.nnov.ru/win/Excel/Downloads
Вопрос
Просматривая базу знаний по MS Excel я наткнулся на полезный совет «Как в Excel связать данные в разных столбцах?».
А как сделать чтобы нажимая на ячейку из него вылезал список, составленный из ячеек, расположенные где-нибудь на этом же листе или на др.листе.
Ответ
Вариант для списка в этом же листе:
— определяемся с тем, где будет или уже есть список значений для ячейки
— выбираем ячейку, в которой должен работать ниспадающий список возможных значений
— Меню-Данные-Значения или Проверка (точно не помню, на англ. — Validation) — Выбирать(Allow) — Список(List) — Источник(Source) — Здесь через "=" указываем область определенного нами места списка значений для этой ячейки.
Вариант для списка из другого листа (будет работать и для первого варианта):
— определяемся с тем, где будет или уже есть список значений для ячеки
— идем на нужный лист и выбираем ячейку, в которой должен работать ниспадающий список возможных значений
— Меню-Вставить-Имя-Определить-Выбрать какое-нибудь имя для области списка-Установить область списка значений для этого имени (Важно, что имя будет определено на листе с настраиваемой ячейкой и с явным указанием имени листа, где находятся значения для ниспадающего списка, иначе не заработает, т.к. ниспадающий список работает только с данными, доступными на текущем листе, а явное указание имени другого листа в определении имени открывает для такого списка косвенный доступ на другой лист)
— Меню-Данные-Значения или Проверка (точно не помню, на англ. — Validation) — Выбирать(Allow) — Список(List) — Источник(Source) — Здесь через "=" указываем только что определенное имя области списка.
Замечания:
— если необходимо строго ограничить значения только теми допустимыми значениями, которые определены списком, но это настаивается в диалоге настройки проверки значений вместе с настройкой Аварийного сообщения на случай, если будет выявлена попытка ввести значение, отличное от допустимого.
— если список допустимых значений может пополняться, то можно заранее указать область всего столбца или его части «с запасом"на возможное пополнение списка.
— ячейки с настроенным ниспадающим списком можно копировать, тогда все настройки списка копируются тоже.
— список допустимых значений может ссылаться на собственный столбец той ячейки, ниспадающий список для которой определяется. Это может довольно удобно при составлении небольших списков. Но еще более удобно может оказаться определение имени на область данных в сводной таблице, работающей на основании заполняемого списка, тогда ниспадающий список может автоматически пополнятья новыми значеними из постепенно составляемого списка данных и кроме того, через настройку сортировки сводной таблицы ниспадающий список всегда будет отсортирован по алфавиту.
— список допустимых значений может определен не только столбцом, но и строкой значений, или явным указанием списка значений прямо в диалоге настройки списка.
Из конференции Expert_FAQ
Вопрос
Программированием меня заставила заняться жизнь. И теперь есть необходимость быстро написать макрос, но у меня пока не хватает знаний это сделать. А теперь к сути проблемы: есть таблица, вида
х х х х
х х х х
х х х z
х х х
…
которая каждый день дополняется одним значением. Это значение добавляется в позицию z, на следующий день в следующую за z (ниже) и так каждый день в течении 30 — 31 дня. Необходимо написать макрос так, чтобы он находил последний столбец, где есть значения, и последнюю строку со значением, а затем копировал это значение в другой лист в известную позицию.
Ответ
Последнюю колонку можно определить следующим образом:
Set SourceRange= …
'Здесь подставить адрес любой непустой ячейки из
'массива данных, лучше всего левый верхний угол.
LastColumn=SourceRange.CurrentRegion.Columns.Count
Как я понимаю, массив — календарь за месяц и в столбце не более 7 значений. Поэтому найти непустую ячейку можно простым перебором.
Например:
LastRow=StartValue
'Наверняка заранее известен номер первой строки
'массива
While cells(LastRow+1,LastColumn) <> Empty
LastRow=LastRow+1
Wend
Ну и теперь скопировать:
sheets(SourceSheet).cells(LastRow,LastColumn).Copy
Sheets(DestSheet).cells(DestRow,DestColumn).Paste
Митрич
Вопрос
Давно мучаюсь вопросом: можно ли сделать так, чтобы суммировались только те ячейки, в которых цифры выделены другим цветом (например синим)?
Ответ
И вот, наконец, выдалось свободное времечко в достаточном количестве, чтобы накидать такой вот текстик (специально продвинутых юзеров типа «глаз-алмаз», поскольку для использования его нужно знать, каким числом обозначается интересующий вас цвет):
Public Function ColorSum(sRange As String, vColor As Variant) As Double
'Written by Alex Juice, 07.10.2001
'
'Вычисление суммы значений ячеек с определенным цветом шрифта
'
'Пример записи в ячейке:
'=PERSONAL.XLS!ColorSum.ColorSum("A1:A7";255)
'(при условии что функция хранится в личной книге макросов
'в модуле «ColorSumModule», хотя можно модуль и не указывать)
'вычислит сумму значений
'ячеек диапазона A1:A7, в которых цвет шрифта красный
'
Dim rng As Range, dblSum As Double, i As Integer
Dim r As Integer, c As Integer, r1 As Integer, c1 As Integer
'Узнаем координаты текущей ячейки (строку и столбец)
r = ActiveCell.Row
c = ActiveCell.Column
Set rng = Range(sRange)
'Перебираем все ячейки заданного диапазона и узнаем их координаты,
'дающие нам смещение от активной ячейки, в которую будет записана сумма
For i = 1 To rng.Cells.Count
r1 = rng.Cells(i).Row
c1 = rng.Cells(i).Column
'Если ячейка с данным смещением имеет цвет шрифта, совпадающий с заданным,
'ее значение включается в сумму
If ActiveCell.Offset(r1 — r, c1 — c).Font.Color = vColor Then
dblSum = dblSum + ActiveCell.Offset(r1 — r, c1 — c).Value
End If
Next i
ColorSum = dblSum
End Function
В дополнение:
Получить число, обозначающее цвет текущей ячейки (для тех, кто на глаз не умеет определять, вроде меня ;-), можно так:
Public Function CellFontColor()
CellFontColor = ActiveCell.Font.Color
End Function
Если в данном диапазоне изменить либо сами цифры, либо цвет на любой другой, то значение в формуле не изменяется сразу, а чтобы оно поменялось, надо нажать, как обычно, F2 и Enter. Тогда формула пересчитается. По крайней мере, у меня так в Office 2000.
Alex Juice, BARCA
Вопрос
Мне необходимо, чтобы из колонки I Листа1 данные, соответствующие критерию, суммировались и переносились в определенную ячейку Листа2.
Пример: данные, находящиеся в колонке «I», соответствующие критериям:
* Категория счета — Неоплачиваемый;
* Д (день) — 6
* М (месяц) — 12
суммировались и переносились в ячейку E3
Т.е., чтобы подсчитывалась «реализация по кухне» для «неоплачиваемого счета» за 1 день в этом месяце, и т.д.
Ответ
Вот это — рабочая процедура (а далее — примеры ее вызовов).
[В конце перенесенных строк ставится (через пробел) знак подчеркивания, т.е." _" (см. следующие две строчки)]
Sub InsertSumm(Optional sCat As String, _
Optional bDay As Byte, Optional bMonth As Byte, _
Optional sResCell As String)
'Задаем имена рабочих листов
Const FirstSheet As String = "Отчет"
Const SecondSheet As String = "Отчет за декабрь"
'А это — порядковый номер столбца, откуда берем данные («I»)
Const MyCol As Integer = 9
'Параметры заданы как необязательные, т.е.
'при вызове процедуры без параметров она их запросит
If sCat = "" Then sCat = InputBox("Введите категорию")
If bDay = 0 Then bDay = InputBox("Введите день")
If bMonth = 0 Then bMonth = InputBox("Введите месяц")
If sResCell = "" Then sResCell = _
InputBox("Введите адрес ячейки для вставки суммы")
Sheets(FirstSheet).Activate
Dim lngRow As Long, dblSum As Double
'Цикл по всем строкам столбца, пока не будет пустой
'Начинаем с третьей, т.к. в первых двух — заголовки
For lngRow = 3 To 65535
'Выделяем ячейку
Cells(lngRow, MyCol).Select
'Если она пустая — заканчиваем цикл и вставляем сумму
If ActiveCell.Value = "" Then Exit For
'Проверяем "соседей" ячейки на условия
'(Ячейки со смещением относительно исходной
'в 7, 5 и 4 столбца)
If ActiveCell.Offset(0, -7).Value = sCat And _
Day(ActiveCell.Offset(0, -5).Value) = bDay And _
Month(ActiveCell.Offset(0, -4).Value) = bMonth Then
dblSum = dblSum + ActiveCell.Value
End If
Next lngRow
'Переходим на нужный лист
Sheets(SecondSheet).Activate
'Выделяем нужную ячейку
Range(sResCell).Select
'Вставляем сумму
ActiveCell.Value = dblSum
End Sub
'Вызов процедуры с заданными параметрами:
'Категория — Клубный, День — 6, Месяц — 12,
'Результирующая ячейка — B3
Sub DoInsert()
InsertSumm "Клубный", 6, 12, "B3"
End Sub
'Вызов процедуры без параметров
'(Параметры будут запрашиваться у пользователя)
Sub PromptInsert()
InsertSumm
End Sub
Alex
Вопрос
Можно ли отключить проверку на вирусы (макрос) при открытии файла в Excel 2000? В 97-м это отключалось, а 2000-й всегда задает глупые вопросы «отключить макросы или разрешить макросы?»
Ответ
Меню Сервис — Макрос — Безопасность. Там поставьте низкий уровень безопасности.
Вопрос
Версия программы: 2000
Иногда, когда таблица содержит пустые поля, при копировании ее на другой лист пустые ячейки заменяются нулями. Как сделать так, чтобы если ячейка содержит значени 0, то она становилась пустой. Смысл — убрать просто нули из таблицы.
Ответ
Для того чтобы нули не отображались войди в меню СЕРВИС — ПАРАМЕТРЫ там находишь вкладку ВИД и внизу, в параметрах окна убираешь галочку с строки НУЛЕВЫЕ ЗНАЧЕНИЯ. И все должно работать.
Из конференции Expert_FAQ
Вопрос
Необходимо удалить в таблице все строки, где в шестом столбце стоит "0". Как это можно реализовать через цикл?
Ответ
Если у тебя таблица непрерывная (без пустых строк, иначе придется еще одно условие задавать), то делается это так:
Sub Test()
Dim i As Long
Application.ScreenUpdating = False
Лист1.Activate
[F1].Select
x = ActiveCell.Offset(1000, 0).End(xlUp).Row
For i = x To 1 Step -1
If Cells(i, 6) = 0 Then
Cells(i, 6).EntireRow.Delete
End If
Next i
Application.ScreenUpdating = True
End Sub
Из конференции Expert_FAQ
Вопрос
Как мне удалить не существующие связи? Например, есть у меня файл, в котором куча ссылок на данные из других файлов.
Варианта два:
1. Я сохраняю этот файл под другим именем, листы со ссылками удаляю.
2. Я копирую нужный мне лист в чистый файл. В обоих случаях я получаю файл в котором нет формул со ссылками на другие файлы, но при открытии этого файла, Excel упорно лезет их обновлять.
Ответ
Меню «Правка», далее «Связи» и в этом окне выбрать все связи и нажать «Разорвать связь». И все ОК!
Из конференции Expert_FAQ
Вопрос
Как удалить связь на другой файл? Как найти какая ячейка ссылается на другой файл?
Ответ
Первым делом войди в меню «Правка» и выбери пункт «Связи», затем перепиши все файлы на которые ссылается данная книга (находится в первой колонке — «Исходный файл»), например ''C:\…\Вексель.xls'' и «Z:\…\Номенклатура.xls». Потом начинаешь искать эти файлы (названия этих файлов), заключенные в квадратные скобки.
В данном случае [Вексель.xls], и [Номенклатура.xls]. Да, кстати о птичках — должны быть сняты флажки (в диалоге поиска) — «Учитывать регистр» и «Ячейка целиком» и установлена «Область поиска» на «Формулы». Я понимаю, что последний совет из разряда «Деточка, если не дышать — то помрешь» ;-), но все же — на всякий случай.
Вадим
Вопрос
Как узнать ID стандартных рисунков, которые можно помещать на иконки?
И можно ли туда помещать собственные иконки из файла(ico или bmp)?
Ответ
CopyFace метод копирует рисунок иконки (по ID) в Clipboard (буфер клавиатуры).
Соответственно, перебрав все ID по циклу можно вытянуть все изображения иконок.
FaceID свойство позволяет узнать или назначить рисунку кнопки его ID (в хелпе есть пример).
PasteFace метод дает возможность вставить в качестве рисунка иконки рисунок, находящийся в Clipboard.
Из конференции Expert_FAQ
Вопрос
Как установить наиболее часто повторяющиеся значения в колонке таблицы средствами VBA for Excel и вывести его текстовое поле TextBox.
Ответ
Dim vMod As Variant, rng As Range
Set rng = Range("A1:A200")
vMod = Application.WorksheetFunction.Mode(rng)
Me.TextBox1.Value = vMod
Проблема только в том, чтобы определить обрабатываемый диапазон. Но если под таблицей ничего нет, то сойдет и 65535 как номер последней строки, т.е. примерно так: Set rng = Range("A1:A65535")
Leshek
Вопрос
Обычно в панели задач на каждый открытый файл EXCEL отображается иконка, но в кокой-то момент происходит сбой в результате которого в панели задач на все открытые файлы EXCEL остается одна иконка. Как вернуть все обратно ?
Ответ
Попробуйте:
Сервис — Параметры — Вид — Отображать — галочку в поле «Окна на панели задач».
Из конференции Expert_FAQ
Вопрос
Есть книга, содержащая несколько десятков листов.
Подскажите, как через макрос автоматизировать поиск по всем листам, т.е. аналог встроенного поиска, но без открытия каждый раз нового листа и вызова поиска по этому листу?
Ответ
Если реч идет о написании макроса ручками на Visual Basic, то такой вот код:
Dim R
As Range Dim FirstMatch
As String FirstMatch = ""
' пока ничего еще не найдено, см. далее
For Each W In Worksheets
' букв. "для каждого из листов книги"
' ищем первое вхождение "zzz", параметры в хелпе есть
Set R = W.Cells.Find(What:="zzz", _ After:=ActiveCell, _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False)
' пока что-то найдено Do While (Not R Is Nothing)
' ***** здесь действия над найденными ячейками *****
' адрес ячейки: строка=R.Row, колонка=R.Column
' **************************************************
' запомнить первый результат на листе
If FirstMatch = ""
Then FirstMatch = R.Address
' поискать еще на этом же листе
Set R = W.Cells.FindNext®
' если нашли то, что уже было, то выход
If FirstMatch = R.Address
Then GoTo NextSheet Loop NextSheet: FirstMatch = ""
' обнулить первый результат Next W
' следующий лист
если же нет, то, боюсь, невозможно, т.к. макрос, написанный «автоматикой» может запомнить имя листа, а не переход на следующий лист.
Из конференции Expert_FAQ
Вопрос
Как, при помощи макроса, изменить часть формулы в одной/ нескольких ячейках?
Суть проблемы: имеется Книга с кучей листов. В каждом Листе есть колонка с однотипными формулами. Захотелось изменить часть формулы, а не целиком. Попробовал записать в макрос последовательность действий, (один из опробованных вариантов: выделяю ячейку, изменяю часть формулы, Enter; все, останавливаю запись; пробовал то же самое и с диапазоном…). Однако, при запуске макроса на другом листе формула как бы копируется из того, в котором писался макрос, а мне требуется только повторение действий с ИЗМЕНЕНИЕМ ЧАСТИ ФОРМУЛЫ, а не всю ее менять. Вот такой вопрос. Заранее благодарю за ответ. Подозреваю простое решение на VBA, но пока только начинаю его изучать.
Ответ
Можно воспользоваться автозаполнением: изменяете в столбце первую формулу. Выделяем ячейку с исправленной формулой. Смотрим в правый нижний угол выделения — там есть маленький черный квадрат — тянем за него мышкой через весь столбец с формулами.
Формула скопируется во все ячейки, причем адреса ячеек и диапазонов ячеек будут скорректированы автоматически, т.е. в формуле в ячейке ниже номера строк в адресах ячеек увеличатся на единицу.
Если существуют ссылки на ячейки, адрес которых надо сохранить неизменным (все формулы ссылаются на одну и ту же ячейку), то для того, чтобы автозаполнение сработало корректно, адрес такой ячейки надо закрепить значками $, например: $A$10. Ну а для того, чтобы правильно работал макрос — после записи его надо еще править, потому как реально в макросе в ячейку записывается формула целиком.
Из конференции Expert_FAQ
Вопрос
Вопрос по созданию пользовательской функции в Excel. Необходимо создать функцию поиска подстроки в строках ячеек, аналог функции FIND. Вопрос — какой тип данных должна возвращать функция после того как в нее передали диапазон ячеек и провили поиск в них? Не будет ли Excel потреблять большие обьемы памяти при учете, если функция будет возрасщать массив?
Ответ
Исходи из того что тебе нужно знать ячеку и позицию в строке. Соотевтственно будет достаточно возвращать масив из двух элементов. Насчет памяти если передовать большой масив, то память жрать будет и немного притормаживать при передаче масива. хотя можно выкрутится передав в функцию вариантовскую переменную по ссылке. А внутри функции ты будешь выделять под нее массив функцией array(…). И возвращать ничего ненадо.
Из конференции Expert_FAQ
Вопрос
Не подскажете, есть ли стандартная функция в EXCEL замены больших английских букв на маленькие, да так, чтобы первая буква оставалась большая, во!
Ответ
StrConv("YOUR TEXT", vbProperCase)
сделает так, что большими останутся только первые буквы каждого слова.
Alex Juice
Вопрос
Подскажите, пожалуйста, комбинацию клавиш для удаления колонки (строки).
Ответ
Из справки:
CTRL+ПРОБЕЛ — Выделение столбца целиком.
SHIFT+ПРОБЕЛ — Выделение строки целиком.
Затем нажмите CTRL+ДЕФИС (Удалить выделенные ячейки).
Из конференции Expert_FAQ
Вопрос
Есть таблица, в которой первая строка представляет шапку ФИО, Номер тел, и т.д, а дальше идет список из 80 человек по одной строке на каждого. Мне нужно как-то сделать, чтобы оно делало на другом листе что-то типа отдельной таблички на каждого человека, шапку переводило в вертикальный вид и вставляло нужные значения (не формулы, а именно вычесленные значения) из ячеек основной таблицы. Чтобы было понятнее, мне приходит екселевский файл от провайдера мобильной связи по корпоративным телефонам, где указано все детально: абонплата, разговоры в сети, исходящие, входящие, СМС и прочая ерунда. В екселе этих колонок от D до AB. Каждый раз делать это самому на 80 человек — 3 дня работы. Может можно как-то автоматизировать.
Ответ
Sub ManyToIndividual()
Dim iRow As Long iRow = 2 Dim ws As Worksheet
Set ws = Application.Worksheets(1)
Do While Not ws.Cells(iRow, 1).Text = "" Dim nws As Worksheet
If Application.Worksheets.Count >= iRow
Then Set nws = Application.Worksheets(iRow)
Else Set nws = Application.Worksheets.Add(After:=Application.Worksheets(Application.Worksheets.Count))
End
If nws.Cells.ClearContents Dim iCol
As Long iCol = 1
Do While Not
ws.Cells(1, iCol).Text = "" nws.Cells(iCol, 1).Value = ws.Cells(1, iCol).Value nws.Cells(iCol, 2).Value = ws.Cells(iRow, iCol).Value iCol = iCol + 1
Loop iRow = iRow + 1
Loop
End Sub
Из конференции Expert_FAQ
Вопрос
Есть — Win 98 , Excel 97 как написать макрос , поиск слова в строке и удаления именно этой строки?
Ответ
Sub DelStrok() Dim te,
r As String
If Selection.Cells.Count > 1
Then Set sel = Selection
Else Set sel = Cells
End If te = InputBox("Строка поиска:", _ "Задать строку поиска для удаления строк")
If te <> ""
Then Err.Clear On Error Resume
Next Do '***
r = sel.Find(What:=te, _ After:=ActiveCell, _ LookIn:=xlValue, _ LookAt:=xlWhole, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False).Address
If Err.Number <> 0
Then Exit Do '****
Else Range®.Activate Rows(Range®.Row).Delete
End If Loop '***
End If
End Sub
*** — ВНИМАНИЕ!!!
Эти строки можно закомментировать или удалить, если нужно, чтобы удалялась только одна первая попавшаяся строка, удовлетоворяющая условию поиска, а не все в выделенной области на листе (или на всем листе, если ничего не выделено особо).
Прим.:
Удаляются только те строки, содержимое значение которых полностью совпадает с заданной строкой поиска. Чтобы все работало еще и по совпадению части стороки, нужно заменить xlWhole на xlPart в параметрах метода Find.
Из конференции Expert_FAQ
Вопрос
Необходимо написать макрос, который создает новый лист и присваивает ему имя, которое записано в некоторой ячейке одного из имеющихся листов. Значение в исходной ячейке меняется (там записана дата). Пробовал записать такой макрос, но при его выполнении листу всегда присваивается то имя, которое было при написании.
Ответ
Макрос:
Sub Макрос4()
'
' Макрос4 Макрос
' Макрос записан 30.10.2002 (dima)
'
'
Dim aSheet As String
aSheet = ActiveSheet.Name
Sheets.Add after:=Worksheets(Worksheets.Count)
Worksheets(aSheet).Select
' имя берется из выделенной ячейки если надо из другой, указать ее
' range("a1").select
Sheets(Worksheets.Count).Name = Selection.Text
Selection = Now()
End Sub
Из конференции Expert_FAQ
Вопрос
Можно ли в Excel работать с комплексными числами?
Ответ
Может
Запусти СПРАВКУ — F1 и найди… КОМПЛЕКСН
См. также
Преобразует коэффициенты при вещественной и мнимой частях комплексного числа в комплексное число в форме x + yi или x + yj.
Если данная функция недоступна или возвращает ошибку #ИМЯ?, установите и загрузите надстройку <Пакет анализа>.
Инструкции
В меню Сервис выберите команду Надстройки.
В списке надстроек выберите Пакет анализа и нажмите кнопку OK.
Выполните инструкции программы установки, если это необходимо.
Синтаксис
КОМПЛЕКСН(действительная_часть;мнимая_часть;мнимая_единица)
Действительная_часть — это действительная часть комплексного числа.
Мнимая_часть — это мнимая часть комплексного числа.
Мнимая_единица — это обозначение мнимой единицы в комплексом числе. Если аргумент мнимая_единица опущен, то предполагается, что он равен «i».
Примечание. Все функции работы с комплексными числами допускают обозначение для мнимой единицы «i» или «j», но не «I» или «J». Использование верхнего регистра приводит к получению значения ошибки #ЗНАЧ!. Все функции, которые используют два или больше комплексных числа, требуют, чтобы обозначение мнимой единицы было одно и то же.
Заметки
Если действительная_часть не число, то функция КОМПЛЕКСН возвращает значение ошибки #ЗНАЧ!.
Если мнимая_часть не число, то функция КОМПЛЕКСН возвращает значение ошибки #ЗНАЧ!.
Если мнимая_единица ни «i», ни «j», то функция КОМПЛЕКСН возвращает значение ошибки #ЗНАЧ!.
Пример
Чтобы этот пример проще было понять, скопируйте его на пустой лист.
Инструкции
Создайте пустую книгу или лист.
Выделите пример в разделе справки. Не выделяйте заголовок строки или столбца.
Выделение примера в справке.
Нажмите сочетание клавиш CTRL+C
На листе выделите ячейку A1 и нажмите сочетание клавиш CTRL+V.
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите сочетание клавиш CTRL+` (апостроф) или в меню Сервис укажите на пункт Зависимости формул и выберите режим Режим проверки формул.
1
3
4
5
A B
Формула Описание (результат)
=КОМПЛЕКСН(3;4) Комплексное число с действительным и мнимым коэффициентами 3 и 4 соответственно (3 + 4i)
=КОМПЛЕКСН(3;4;"j») Комплексное число с действительным и мнимым коэффициентами 3 и 4 соответственно и мнимой единицей j (3 + 4j)
=КОМПЛЕКСН(0;1) Комплексное число с действительным и мнимым коэффициентами 0 и 1 соответственно (i)
=КОМПЛЕКСН(1;0) Комплексное число с действительным и мнимым коэффициентами 1 и 0 соответственно (1)
Из конференции Expert_FAQ
Вопрос
Можно ли внутрь xls-файла поместить ехе-файл и выполнить его когда надо?
Ответ
Можно.
Меню-Вставить-Объект-Из файла-Выбрать нужный файл. Далее, чтобы запустить — выделить объект с файлом, далее:
Меню-Правка-Упакованный Объект-Активировать Содержимое
(Возможно небольшие разночтения в именах пунктов меню, т.к. у меня английская версия)
Из конференции Expert_FAQ
Вопрос
Вопрос заключается в следующем:
В Excel'е не хватает столбцов, возможно ли их добавить? Ниже строится в автомате график, поэтому, в другой лист перебрасывать не подходит. В итоге предполагается таблицу и график перебросить в Сorel..
Ответ
Более 255 столбцов на листе быть не может
Из конференции Expert_FAQ
Вопрос
Можно ли защитить отдельно взятую ячейку (интервал ячеек) ?
Ответ
Можно, но немного по-другому — можно защитить лист в котором защита ячеек отключена кроме тех ячеек, которые нужно защитить. Допустим нужно защитить только A1, а все остальные ячейки листа оставить изменяемыми, тогда:
— выделить все ячейки листа — Ctrl+A
— формат ячеек — Ctrl+1
— защита — отключить защиту
— перейти в A1
— формат ячейки — Ctrl+1
— защита — включить защиту
— включить защиту листа — меню — сервис — защита — защитить лист.
Из конференции Expert_FAQ
Вопрос
Не открывается файл в Excel'е — выдает: «Excel has generated errors and will be closed by Windows. You will need to restart the program. An error log has been created.»
Где этот обещанный лог я так и не нашла (В EventViewer'e его нет)… В самом файле ценно не содержание, а макросы. Возможно ли как — то «выцепить» макросы из этого кривого файла, или может все — таки возможно его открыть? Открывать пробовали и в 2000 офисе и в 97 (и на разных операционных системах) — результат один и тот же.
Ответ
Есть такая программа Office recovery, она восстанавливает испорченные файлы MS Offise.
Ploot
Вопрос
Хочу удалит макрос в окошке «Макросы», а Excel ругается, что «Изменить макрос в открытой книге невозможно. Закройте все документы и выберите команду «Показать» в меню «Файл». Там такой команды нет вообще. Так как все таки удалить макрос?
Ответ
Зайди в редактор VB Excel (alt+ F11)
Там в разделе «модули» удали все, что мешает. Экспорт модулей перед удалением выполнять или нет — дело твое.
Еще один вариант — если макрос запускается одновременно с открытием файла — предварительно включи защиту от макровирусов: Сервис — Параметры — вкладка Общие. И макросы отключи, тем самым получишь права на удаление макросов (так как запущенный макрос удалить нельзя!)
Дмитрий
Вопрос
Есть у меня макрос: по кнопочке на панельке открывается определенный файл. Все работает. Захотел я назначить макросу горячие клавиши. Делаю так: Alt+F8 — Параметры — Сочетания клавиш. Ставлю «Ctr+д». Закрываю… и никаких изменений: по Ctr+д макрос не выполняется.
Ответ
В горячей клавише нужно писать английскую букву, а не русскую. Тогда будет работать и с английской, и с русской. Это сделано для совместимости макроса с разными версиями Excel. Чтобы можно было работать с макросом, нужно писать не «д» а «l».
Вопрос
Имеется БД по клиентам (приход, расход, дата и т.д.), организованная в книгу с несколькими десятками листов. Ежедневно поступает новая информация по клиентам (сумма, дата, номер накладной). Как сделать макрос так, чтобы эта информация записывалась автоматически в карточку каждого клиента в следующие пустые ячейки, определяя нужного клиента по коду (наименованию)? Может существует решение такой проблемы, если на каждого клиента существует отдельный файл, имя которому — код клиента?
Ответ
Вот что у меня получилось, проверено в Excel2000 На всякий случай: комментарии относятся к нижележащей строке Этот текст нужно в редакторе Visual Basic (Alt+F11) вставить в пустой модуль (Insert — Module)
Option Explicit
'Здесь запишите адрес папки, в которой хранятся приходящие файлы по клиентам
Const MyFolder As String = "C:\" 'Количество полей (столбцов), в которые записывается информация Const FieldsCount As Integer = 5
'Имя файла, в котором хранится вся база
'Он должен быть в папке, отличной от MyFolder,
'иначе файл базы попадет в список клиентских
'что вызовет ошибку
Const MyBase As String = "C:Temp\base.xls" 'А это имя базы без пути
Const BaseName As String = "base.xls"
Sub WriteInfo()
Dim Files() As String, i As Integer, fs As FileSearch, strClient
As String, bOpened As Boolean
'Проверим, открыта ли база
For i = 1 To Application.Workbooks.Count
If Workbooks(i).Name = BaseName Then
bOpened = True
Exit For
End If
Next i
If bOpened = False Then Workbooks.Open MyBase
Set fs = Application.FileSearch
'Поиск пришедших файлов в папке
With fs
.LookIn = MyFolder
.FileName = "*.xls"
End With
'Получение имен файлов
If fs.Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
ReDim Files(1 To fs.FoundFiles.Count)
For i = 1 To fs.FoundFiles.Count
Files(i) = fs.FoundFiles(i)
Next i
Else
'Если в папке пусто, выполнение останавливается
'(Не забывайте очищать папку после каждого сеанса работы)
MsgBox "Новых файлов не найдено"
Exit Sub
End If
For i = 1 To UBound(Files)
'Получение имени листа откидыванием расширения от имени файла
strClient = Left$(Files(i), Len(Files(i)) — 4)
Workbooks.Open Files(i)
'(Предполагается, что в новых файлах только по одному листу,
'если это не так, пишите, сделаем по-другому)
'Также предполагается, что база заполняется, начиная с первого столбца
'(это первая единица в следующей строке)
Range(Cells(1, 1), Cells(1, 1).Offset(FirstEmptyCell — 2, FieldsCount — 1)).Select
Selection.Copy
Workbooks(BaseName).Activate
Worksheets(FileName(strClient)).Activate
Cells(FirstEmptyCell, 1).Activate
ActiveSheet.Paste
'Это делается для предотвращения появления сообщения
'о большом объеме информации в буфере обмена при закрытии файла
Cells(1, 1).Select
Selection.Copy
Workbooks(FileName(Files(i))).Close False
Next i
End Sub
'Функция нахождения первой пустой строки
Function FirstEmptyCell() As Long
'Это столбец, который будет проверяться на пустые ячейки
'Введите вместо единицы номер столбца,
'для которого в записях никогда не бывает пустых ячеек
Const Col As Integer = 1
Dim i As Long
For i = 1 To 65535
Cells(i, Col).Select
If ActiveCell.Value = "" Then
FirstEmptyCell = i
Exit Function
End If
Next i
End Function
'Получение имени файла из полного имени
Function FileName(FullName As String) As String
Dim i As Integer
For i = Len(FullName) To 1 Step -1
If Mid$(FullName, i, 1) = "\" Then
FileName = Mid$(FullName, i + 1)
Exit Function
End If
Next i
End Function
Alex Juice
Вопрос
Пример:
за прошлый год у меня было к примеру 26, за этот 7. Мне нужно из 26 вычесть 7 и результат (19) вывести как -19. Т.е. объемы продаж уменьшились на 19. Если второе число больше, мне нужно чтобы в результате перед числом стоял +
Пример: 7 за прошлый 26 за текущий, итог +19
Как это сделать практически?
Ответ
А что мешает из 7 вычитать 26? Знак получится автоматически. Чтобы у положительных чисел показывался знак "+" надо проделать следующие действия (так как стандартного формата такого у Екселя нет):
Выделяйте ячейки с посчитанной разницей. Далее идем в меню Формат — Ячейки…
Переходим на закладку Число.
Выбираем из списка форматов пункт (все форматы), и в поле Тип пишем:
+0;-0 (или +0.00;-0.00 если надо округлять до двух знаков после запятой, например).
Якушев Антон Юрьевич
Вопрос
Я столкнулся со следующей проблемой: был файл с макросом, открываю новый, создаю точно такой же макрос, присваиваю хоткей. Жму — не работает. В старой книге с макросом нет никаких проблем. Пробовал скопировать макрос из «старой» в новую книгу- тот же отрицательный результат. Вопрос: в чем проблема?
Ответ
Попробуй запустить этот макрос не через hotkey, а из меню или редактора вб. Если он и там не запускается, значит или стоит отключение макросов в параметрах безопасности, или Excel'овские хоткеи глючат (такое, к сожалению, бывает иногда). В первом случае отключи, во втором попробуй переставить.
DmitryK1
Вопрос
При защите Excel-книги паролем она перестает сжиматься архиваторами. Точнее, сжимается процентов на 10. В то время как при защите без пароля — в 10 раз. Почему это происходит?
Ответ
Дело в том, что при защите книги паролем она шифруется. Причем для уменьшения вероятности взлома алгоритм шифрования построен таким образом, чтобы на выходе получались неповторяющиеся данные (ну, например, при просмотре нешифрованного файла Excel в каком-либо шестнадцатеричном редакторе можно увидеть длинные последовательности нулей, в шифрованном варианте их нет или намного меньше). Архиваторы же основаны на том принципе, что длинная последовательность повторяющихся данных, заменяется на более короткую и счетчик ее повторений. Таким образом, зашифрованный файл для архиваторов — не самые удобные данные. Преодолеть можно, например, архивируя незашифрованный файл с паролем на архив.
A.Z.
Вопрос
Excel 97. Win2000.
Работаю с MS Office давно, но такого не было. А в последнее время книги Excel'я самопроизвольно переименовываются в файлы без расширения, например C1358000 :(. Часто этот файл можно открыть при помощи Excel'я и переименовать. Но не так уж редко и не возможно. Данные теряются. :( Мне кажется, что это стало происходить после подключения компа к локалке. И, по-моему это происходит с общими файлами.
Ответ
Это может происходить действительно из-за локалки, если с другого компьютера идет сохранение и в это время обрывается по какойто причине, то соответственно файл остается не дописанным.
Из конференции Expert_FAQ
Вопрос
Написал макрос для перехода на нужный лист
Sub Кнопка1_Щелкнуть()
Sheets("Лист1").Select
End Sub
и назначил его кнопке (таких кнопок у меня 26 — по количеству листов). При нажатии мышкой на кнопку появляется сообщение «Can't execute code in break mode». Чтобы это значило и как исправить ошибку?
Ответ
Вызывая Sheets ("Лист1"). Select ты обращаешься к листу с конкретным именем. Если ты изменил имя листа, то работать эта штука не будет. Пробуй через номкер листа. Нумерация начинается с 1. Sheets(1).Select 'обращаемся к листу номер 1 и неважно какое у него имя, главное что он первый
Из конференции Expert_FAQ
Вопрос
Win 2000. Excel 97.
Работает пользователь в локальной сети с файлом Excel'я с моего диска. У меня сбой — машина самопроизвольно перезагружается. После перезагрузки все нормально: он меня видит, я его вижу. Но файл он сохранить не может. Причем вообще никак не может, даже на свой диск.
Ответ
На машине, которая перезагрузилась остался временный файл рабочего файла. В нем сохранились послендние изменения. А следующие изменения файла тебе восстановить никак не удасться :(
Из конференции Expert_FAQ
Вопрос
У меня Excel 2002 SP2 русская версия. Когда ввожу в ячейку, например, вот такие данные «3-2-12» (без кавычек), то после нажатия клавиши Enter, они преобразуются в 03.02.2012. Кликаю правой кнопкой мыши, нажимаю Формат ячеек…, в Числовых форматах стоит
Дата, ставлю Общий, нажимаю OK, а он мне в ячейку вместо 03.02.2012, вставляет 40942 (такой образец). Опять я пытаюсь ввести 3-2-12, а дальше все по сценарию. Что делать? Может что в параметрах Excel отметить? В принципе, если писать 3-2-12 с точкой на конце, то все OK, но мне это не подходит. ПОМОГИТЕ!
Ответ
В принципе можно сделать. Все зависит от того, какие именно данные ты подразумеваешь под 3-2-12 если это формула то перед ней ставь равно, если простой текст, то перед тем как вводить в ячейку текст сделай ей формат текстовым, а не общим. Общий, это автоматически определяемый, если он считает что число похоже на дату то преобразует, а текстовой конкретно уже считается как текст.
У меня обычно бывает что нужно отельный столбик вводить такие данные, так я тыкаю в имя столбика, (*буковка сверху) и выбираю формат всего столбца, потом вводя уже не мучаюсь.
Из конференции Expert_FAQ
Можно перед вводимым текстом вставить апостроф ('), его самого видно не будет, а все, что введено после, будет отображаться, как текст.
Вопрос
У меня не получается сделать следующее:
при заполнении товарного чека (дата, отдел, наименование, кол-во, сумма) надо, чтобы в другой лист(отчет) переносились в следующую строку(пустую) данные (дата, наименование, сумма).
Проблема не в простом переносе ячеек, а в необходимости добавления этих ячеек(получается строки) ниже последней (строки).
Ответ
Очень поможет стандартное копирование — выделяем что скопировать, правой кнопкой — копировать, на листе КУДА надо вставить так же выделяем место в которое надо вставить — только не одну ячейку, а по размеру будущего текста — и так же правой кнопкой — СПЕЦИАЛЬНАЯ ВСТАВКА. Указываем параметры вставки: копирование ЗНАЧЕНИЙ и С ТРАНСПОНИРОВАНИЕМ.
Из конференции Expert_FAQ
Вопрос
Посоветуйте ресурсы в Инете по изучению Excel.
Ответ
Самый большой ресурс — это файл справки (и это не шутка). Более полной информации о функциях и событиях VBA ты не найдешь, а также по функциям листа Excel.
Лучший вариант, если есть какие-то вопросы, то воспользоваться специализированным форумом, где можно будет задать свои вопросы и получить достаточно быстрый ответ.
Вот пару ссылок на форумы:
Форум по VB и VBA (тебе VBA, здесь по большей части макросы для Excel обсуждают, хотя Word и Access также присутствуют):
http://bbs.vbstreets.ru/
Здесь уже форум по нескольким языкам прогр-я и не только, VBA среди них тоже есть:
http://www.relib.com/
Ну, и конечно, на http://www.microsoft.ru
можно в разделе Office'a выбрать Excel и ознакомиться с некоторыми возможностями. Также Knowldege Base (Microsoft'кий) стоит посетить, если возникли конкретные вопросы.
P.S. Можешь по ходу дела разбираться с VB, все таки VBA — это прикладной VB для приложений офиса (Visual Basic for Applications). Потом можно и на другие языки переходить.
Из конференции Expert_FAQ
Вопрос
При работе в Exсel2000 c некоторых пор заметил резкое увеличение времени загрузки/выгрузки файлов c/на диск. При объеме файла 150 кбайт время загрузки составляет 5 мин, при объеме файла больше 2 Мбайт время загрузки — более часа. Как изменить ситуацию?
Ответ
Проблема, скорее всего, заключается в содержании документа: есть ли ссылки на другие документы в вашем документе Excel? Есть ли внедренные объекты (картинки)? Попробуйте уменьшить число и тех, и других.
Степанов Эдуард
Возможно в каталоге
C:\Documents and Settings\пользователь\Local Settings\temp находится слишком много файлов(я решил такую проблему именно так).
Copyright 
2000-2004 Сообщество Чайников
Контактная информация