Специальные акции
Скидки
Компьютерные курсы
Начинающим пользователям Уверенным пользователям Схема обучения Расписание и стоимость Отзывы слушателей Сертификации Колонка преподавателя
Условия обучения
Преподаватели Документы об обучении Дистанционное обучение
Обратная связь
Контакты Форум
Другие направления

Колонка преподавателя

MS Excel: Кладезь ответов

Есть множество инструментов, которые помогают человеку в его работе: молоток, токарный станок, осциллограф, тонометр, компьютер… Каждым инструментом можно пользоваться с разной долей мастерства. У каждого инструмента есть множество хитростей, которые знает только эксперт, проработавший с этим инструментов настолько много, что теперь знает о нем все. Но даже такие эксперты зачастую открывают для себя что-то новое.

Все это в полной мере относится и к компьютерным программам. Казалось бы, что удивительного можно обнаружить в MS Word или MS Excel. Но пользователи задают вопросы, которые иногда ставят в тупик даже профессионалов. Вот бы поучиться на чужом опыте, прочитать такую книгу, чтобы сразу все премудрости и хитрости освоить…

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

Читайте, изучайте, добавляйте!

Общие вопросы

Вот давно интересно узнать, как отключить в Word и Excel из пакета MS Office, "выскакивание" буфера обмена?
На самом деле очень просто, нужно только повнимательнее присмотреться к области задач Буфер Обмена  (это та, которая появляется обычно справа).
Так вот, в нижней части этой области есть кнопка Параметры, щелкнув по которой, необходимо настроить два флажка:
- Автоматическое отображение Буфера Обмена MS Office,
- Собирать данные без отображения буфера Office.
PS: область задач буфера обмена при желании можно включить через меню Правка.
Иногда коллеги спрашивают про то, как вернуть в Excele названия столбцов "буковками", уж что они делают, чтобы нумеровать столбики циферками, - не знаю.
Скорее всего, выбран другой стиль ссылок, его называют R1C1. Тогда нумеруются как строки, так и столбцы: Excel указывает положение ячейки буквой «R», за которой идет номер строки, и буквой «C», за которой идет номер столбца.
Ничего плохого в этом нет, иногда так даже удобнее. В любом случае, настройка через Сервис-Параметры, вкладка Общие. Там сразу флажок про стиль ссылок, который, видимо, нужно снять.
Как получить графическую копию ячеек.
Выделите ячейки с какой-либо информацией (можно например выделить всю таблицу). Скопируйте ячейки в буфер обмена.
Перейдите в нужное место рабочей книги, туда, где Вам нужна графическая копия этих ячеек. Зайдите в меню Правка с нажатой клавишей SHIFT и щелкните по команде Вставить рисунок. Готово!
Еще более интересный эффект получается, когда применяется команда Вставить связь с рисунком. В этом случае, при изменении оригинала (данных в ячейках или форматирования) будет автоматически меняться и копия.
Применяется данная возможность, например, при создании нескольких таблиц с разной разметкой (возможно сложной) на одном рабочем листе.
Как получить графическую копию ячеек.

Форматирование

Есть таблица в Excel, в которой в первом столбце (столбец А) идут названия изделий, во втором столбце (столбец B) – названия деталей, которые входят в состав изделия, в третьем столбце (столбец C) – стоимость деталей (1 штуки). Причем эта стоимость может быть как положительным числом, так и отрицательным.
Необходимо выделить детали того изделия, у которого сумма стоимостей по всем деталям больше 100 или меньше -100.
1. Выделяем столбец со стоимостью деталей.
2. Выполняем: Формат \ Условное форматирование.
3. В левом списке указываем пункт Формула. И вводим следующую формулу:
=ABS(СУММЕСЛИ($A$2:$A$20;A2;$C$2:$C$20))>100
Предполагается, что диапазон выделения от 2 до 20 строки.
Цвет чисел будет автоматически изменяться при редактировании данных.
Пример:
В таблице MS Excel введены даты.
Задача.
Необходимо выделить цветом те даты, которые являются выходными (суббота, воскресенье).
1. Выделяем ячейки с датами (допустим от A1 до G1).
2. Выполняем Формат \ Условное форматирование.
3. Выбираем в списке слева Формула. И вводим такую формулу:
=ДЕНЬНЕД(A1;2)>5
Задаем цвет символов с помощью кнопки Формат.
Пример:
Есть документ - в виде таблицы на каждой странице.
Задача вставить под таблицей некую подпись чтобы всё это поместилось на страницу - по горизонтали.
Попытка объединить ячейки под таблицей и сделать перенос по словам - приводит к тому что видно только одну строчку - то есть автоматически не происходит увеличение высоты ячейки. А это необходимо так как речь идёт о создание подобного шаблона для дальнейшей работы.
Попытка вставить графический элемент текст - приводит к тому же - он отлично автоматически выравнивается по горизонтали - но не по вертикали, попытка зафиксировать его размер по горизонтали приводит к тому что видно опять 1 строчку - и приходиться всё делать руками...
Вместо прямого объединения ячеек сделайте следующее: выделите нужное количество ячеек, т.е. в нашем варианте это ширина таблицы и в формате ячеек в выравнивании по горизонтали задайте "по центру выделения", оставьте уже стоящую галочку "переносить по словам" и в формате строки: "автоподбор высоты".
На рабочем листе MS Excel необходимо выделить цветом фона ячейки, например на четных строках. Т.е. сделать разлинованный лист, для более удобного чтения данных. Сделать это нужно для всех строк на листе, и так, чтобы при вставке или удалении правильный формат оставался.
1. Выделяем все ячейки на рабочем листе. Например, CTRL+A.
2. Выполняем Формат \ Условное форматирование.
3. Выбираем в списке слева Формула. И вводим такую формулу:
=ОСТАТ(СТРОКА(A1);2)=0
Задаем цвет заливки с помощью кнопки Формат.
Пример:
Как в зависимости от условия в ячейки А1, например, менять заливку ячейки, скажем B1? т.е. если А1=1 то B1 - красная, если 2 , то B1 - зеленая ну вот в таком роде..
если это невозможно сделать формулами, то как сделать с помощью макросов или VB?
Если вариантов цветов не более трех, то проще делать через условное форматирование.
Для ячейки B1 задать условный формат такой:
=$A$1=1
предварительно выбрав пункт Формула в крайнем левом списке.
Если вариантов цветов больше трех, тогда использовать макросы.

Формулы: выполнение вычислений

Подскажите пожалуйста как c помощью Excel рассчитать прогнозируемые продажи за сентябрь.
Например :
январь 15000
февраль 22000
март 17000
апрель 29000
май 21000
июнь 32000
июль 18000
август 23000
(Функция РОСТ почему-то не получается)
А названия месяцев как введены? Как просто текст? Если да, то попробуйте ввести как даты, и установить формат отображения, чтобы был виден только месяц.
В функциях прогнозирования интервал, для которого предсказываются значения (известные значения X), должны быть числами (или датами в формате Excel).
Необходимо сделать следующее:
есть столбец(ячейки, к примеру, С1..С10, с числами). Как из каждого числа каждой ячейки вычесть 1%(вообще ячеек много, т.ч. вручную не получится)?
Например, такой способ.
1. Во вспомогательной ячейке ввести значение 0,99.
2. Скопировать это значение в Буфер обмена.
3. Выделить все целевые ячейки (в столбце С). Кстати, быстро выделить все ячейки в столбце можно так: щелкнуть по ячейке первой и нажать на CTRL+SHIFT+стрелка вниз.
4. Выполнить Правка \ Специальная вставка \ кнопка Умножить.

Формулы: работа с текстом

В столбце (столбец A) документа Excel введены фамилия, имя, отчество. в соседние ячейки поместить подстроки введенной строки: в столбце А - фамилию, в столбце В - имя, в столбце С - отчество.
1. Вводим в столбце В формулу (выделяем фамилию):
=ЛЕВСИМВ(A2;ПОИСК(" ";A2;1)-1)
2. Вводим в столбце В формулу (выделяем имя):
=ПСТР(A2;ДЛСТР(B2)+2;ПОИСК(" ";A2;ДЛСТР(B2)+2)-ДЛСТР(B2)-2)
3. Вводим в столбце С формулу (выделяем отчество):
=ПРАВСИМВ(A2;ДЛСТР(A2)-ДЛСТР(B2)-ДЛСТР(C2)-2)
Пример:
Если мне нужно из строки "Иванов Иван Иванович" сделать "Иванов И.И." ?
Вот формула:
=ЛЕВСИМВ(A2;ПОИСК(" ";A2;1)+1)&". "&ПСТР(A2;ПОИСК(" ";A2;ПОИСК(" ";A2;1)+1)+1;1)&"."
Предполагается, что исходная строка в ячейке A2.
Два но:
1. Если использовать вспомогательные ячейки, тогда формула будет не такая громоздкая.
2. При желании это можно сделать с помощью макроса (конечно элегантнее).

Формулы: работа с датами

Необходимо в списке данных в MS Excel по дате рождения человека определять его принадлежность к определенной возрастной категории (например, для расчета зарплаты или надбавки или т.п.). Причем коридоры возрастов разные для мужчин и для женщин.
Предварительно сделать две подстановочные таблицы: для мужчин и для женщин. В каждой такой таблице будет границы коридоров возраста в одном столбце и категории (например, 1, 2, 3…) в другом. Вообще-то, можно обойтись и одной таблицей, если границы для М и Ж совпадают, различаются только сами категории.
Например,
Дата_рождения	Категория
1/1/1950		1
1/1/1960		2
1/1/1970		3
1/1/1980		4
1/1/1990		5
Далее, в самой таблице с данными о сотрудниках в столбце для определения категории ввести следующую функцию:
=ВПР(A2;$E$1:$F$7;2;ИСТИНА)
В данном случае,
A2 – это ячейка с датой рождения человека,
$E$1:$F$7 – адрес подстановочной таблицы.
Но это пример для обоих полов. Если нужно сделать различия по полу, тогда немного формула усложнится.
=ЕСЛИ(C2="М";ВПР(A2;$E$1:$F$7;2;ИСТИНА);ВПР(A2;$G$1:$H$7;2;ИСТИНА))
Т.е. используем функцию ЕСЛИ, в которой проверяем пол: C2="М" и в зависимости от результата проверки подставляем первую или вторую функцию ВПР. Они одинаковые, различаются только ссылками на подстановочные таблицы.

Создание макросов с использованием VBA

Пишу макрос для эксела со сложной сортировкой строк. Уперся в один очень простой, но для меня оказавшийся очень сложным вопрос: Как определить, что ячейка пустая средствами VB?, С чем надо сравнивать значение ячейки?
Такой способ:
		If ActiveCell.Text = "" Then
MsgBox ("Пустая")
Else
MsgBox ("Не пустая")
End If
Или:
		If Len(ActiveCell.Text) = 0 Then
MsgBox ("Пустая")
Else
MsgBox ("Не пустая")
End If
Объявляю массив типа Integer. Хочу заполнить его простым перечислением элементов. Перекопал весь Internet, но к сожалению так и не нашел ответа.
Если имеется ввиду просто заполнение элементов массива, то можно использовать цикл. Например так.
    Dim arrX(9) As Integer
Dim intC As Integer
For intC = 0 To 9
arrX(intC) = intC
Next