Электронные таблицы Microsoft Excel (Раздел 5)

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

При использовании электронной таблицы экран компьютера представляется большой таблицей, состоящей из ячеек, организованных прямоугольной координатной сеткой. Колонки обозначены буквами (A. Z, AA. AZ, и т.п.), а ряды — числами (1. 65536).

Адрес ячейки определяется ее местоположением в таблице. Ячейка задается своими координатами, в которых на первом месте стоит буква, обозначающая колонку, а на втором — число, обозначающее ряд. Например, А1 — ячейка в левом верхнем углу, D5 — ячейка на пересечении 4-го столбца и 5-й строки.

Ячейка, в которую в данный момент вводятся данные, называется активной. Она маркируется табличным курсором (выделяется цветом). В каждый момент времени активной может быть только одна ячейка, она всегда выводится на индикацию.

 ввод, хранение и корректировку большого количества данных;

 автоматическое проведение вычислений при изменении исходных данных;

 дружественный интерфейс (средства диалога человека и компьютера);

 наглядность и естественную форму документов, представляемых пользователю на экране;

 вывод на печать профессионально оформленных отчетов;

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

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

Что такое Microsoft Excel. Программа Excel входит в пакет Microsoft Office и предназначена для подготовки и обработки электронных таблиц под управлением Windows..

Документом (т.е. объектом обработки) Excel является файл с произвольным именем и расширением .XLS. В терминах Excel такой файл называется рабочей книгой (Workbook). В каждом файле XLS может размещаться от 1 до 255 электронных таблиц, каждая из которых называется рабочим листом (Sheet).

Для указания на конкретную ячейку таблицы мы используем адрес, который составляется из обозначения столбца и номера строки, на пересечении которых эта ячейка находится (например, A1, F8, С24, АА2 и т.д.). В некоторых табличных процессорах ячейка называется клеткой, а адрес — координатами клетки.

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

Запуск и завершение программы Excel выполняется любым из стандартных способов. Например, для запуска программы можно щелкнуть кнопку «Пуск» и в Главном меню Windows, выбрать команду Microsoft Excel. После этого на экране раскроется окно, как показано на рис. 1.

Если в момент закрытия окна Excel обнаружит, что вы внесли изменения в документ, но не сохранили его в файле, на экран поступит диалоговое окно. Вы можете сохранить изменения в файле (ответ Да), не сохранять изменения (Нет) или продолжить редактирование (Отмена).

Основные принципы создания, загрузки и сохранения документов Excel аналогичны принципам создания, загрузки и сохранения документов в текстовом процессоре Word.

Создание, загрузка и сохранение файлов-документов (книг)

Основные принципы создания, загрузки и сохранения документов Excel:

1. Если вы запустили Excel без аргумента командной строки (т.е. не указав имя файла), программа по умолчанию предлагает вам начать создание нового документа под условным наименованием Книга1.

2. Вы можете подготовить документ (или часть документа), а затем сохранить его на диске (команда Файл-Сохранить как. ) в виде файла с произвольным именем и расширением .XLS. Сохранение файлов-документов выполняется по стандартным правилам Windows.

3. Если вы хотите продолжить обработку уже существующего файла-документа, выберите команду Файл-Открыть. . Откроется стандартное диалоговое окно, в котором нужно выбрать папку и указать имя файла .XLS для обработки.

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

4. Иногда вы редактируете попеременно несколько файлов-документов. Excel, как и Word, запоминает имена файлов, которые вы обрабатывали за последнее время, и выдает их на экран в качестве команд пункта меню Файл. Число файлов в списке определяется пользователем и не превышает 9. Для открытия любого из таких файлов достаточно щелкнуть мышью на его имени в меню Файл.

5. Если вы решили создать новый документ, выберите команду Файл-Создать. , затем выберите шаблон и щелкните «ОК». Для создания нового документа можно щелкнуть кнопку «Создать» в панели инструментов Стандартная.

6. Как и Word, Excel является многооконной программой. Это означает, что вы можете одновременно открыть несколько документов, причем часть из них может быть готовыми файлами, а часть — заготовками (без имени, но с обозначениями Книга1, Книга2 и т. д.).

Рис. 2. Состояние строки формул во время ввода данных

В левой части заголовка столбца (или в верхней части заголовка строки) имеется пустая кнопка для выделения всей таблицы.

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

Панели инструментов. Работая с Excel, вы будете использовать, в основном, две панели инструментов: панель Стандартная и панель Форматирование. Большинство кнопок на этих панелях выполняют те же функции, что и в соответствующей панели редактора Word. На рис. 2.3 показаны кнопки панелей инструментов Excel, отличающиеся от кнопок в Word:

Рис. 3. Назначение кнопок панелей инструментов Excel, отличающихся от кнопок в Word: Автосумма, мастер функций, сортировка по возрастанию, сортировка по убыванию, мастер диаграмм, объединить и поместить в центре, денежный формат, процентный формат, формат с разделителями, увеличить разрядность, уменьшить разрядность

В программе Excel имеются контекстные меню, которые вызываются правой кнопкой мыши, когда промаркирована (выделена) некоторая область таблицы. Эти меню содержат много команд обработки и форматирования таблиц. Команды форматирования можно также вызвать на панели форматирования (вторая строка пиктографического меню на рис. 1), щелкнув мышью по соответствующей пиктограмме.

Чтобы сослаться на диапазон ячеек (например, на группу смежных ячеек в строке), можно указать через двоеточие адреса начальной и конечной ячейки в диапазоне. Например, обозначение А7:Е7 адресует ячейки А, В, С, D, Е в строке 7, обозначение ВЗ:В6 адресует ячейки 3,4, 5, 6 в столбце В и т. п. Диапазоном можно обозначить и блок ячеек. Например, можно написать C6-G9.

Наконец, в Excel предусмотрен очень удобный способ ссылки на ячейку с помощью присвоения этой ячейке произвольного имени. Имя должно начинаться с буквы и содержать не более 255 символов. Не допускается использование пробелов. Имя группы не должно совпадать с адресами ячеек (A1, G4 и т.п.). В этом окне приводится также список уже присвоенных групповых имен, относящихся к данной книге.

Примечание. Присвоить имя можно командой Вставка-Имя-Присвоить.

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

Завершив ввод данных, вы должны зафиксировать их в ячейке любым из трех способов: нажав клавишу Enter или щелкнув на кнопке с галочкой, или щелкнув мышью на другой ячейке.

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

Если вы хотите внести исправления в данные текущей ячей-ми, то нажмите клавишу F2, при этом активизируется строка формул, в которой отображается содержимое текущей ячейки для редактирования.

Чтобы изменить шрифт ячеек, нужно выделить ячейки, а затем воспользоваться кнопками на панели Форматирование или вызвать команду Ячейка из меню Формат. В диалоговом окне Формат ячеек выбрать вкладку Шрифт, в которой можно выбрать из предлагаемого списка вид шрифта и задать другие параметры форматирования символов.

Изменение ширины столбца и высоты строки. Ширина столбца измеряется в символах. Например, ширина 8,5 означает, что в ячейку данного столбца можно ввести в среднем 8,5 символов стандартного шрифта (для пропорционального шрифта точная оценка невозможна). Ширина столбца — от О до 255 символов.

Чаще всего вам придется изменять ширину столбца (например, чтобы разместить тексты или использовать длинные числа).

Если вы хотите явно задать ширину столбца (в символах) или высоту строки (в пунктах), выделите хотя бы одну ячейку в столбце или строке, затем выберите команду Формат-Столбец-Ширина. или Формат-Строка-Высота. и укажите в соответствующем диалоговом окне необходимое значение ширины столбца (высоты строки).

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

Excel адрес диапазона ячеек

1. Выберите инструмент «Формулы»-«Определенные имена»-«Присвоить имя».
2. Заполните поля диалогового окна «Создание имени» как на рисунке. Обратите внимание на то, что в поле «Диапазон:» мы используем функцию =СМЕЩ, а в одном из ее параметров используем функцию =СЧЕТ. Пример: =СМЕЩ(Лист1!$B$2;0;0;СЧЁТ(Лист1!$B:$B);1)
3. Переместите курсор в ячейку D2 и введите функцию =СУММ с именем «доход» в ее параметрах.

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

Excel функция повторяющиеся значения

В некоторых случаях повторяющиеся данные могут быть полезны, но иногда они усложняют понимание данных. Используйте условное форматирование для поиска и выделения повторяющихся данных. Это позволит вам просматривать повторения и удалять их по мере необходимости.

Выберите ячейки, которые нужно проверить на наличие повторений.

Примечание: В Excel не поддерживается выделение повторяющихся значений в области «Значения» отчета сводной таблицы.

На вкладке Главная выберите Условное форматирование > Правила выделения ячеек > Повторяющиеся значения.

В поле рядом с оператором значения с выберите форматирование для применения к повторяющимся значениям и нажмите кнопку ОК .

Удаление повторяющихся значений

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

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

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

На вкладке Данные нажмите кнопку Удалить дубликаты и в разделе Столбцы установите или снимите флажки, соответствующие столбцам, в которых нужно удалить повторения.

Например, на данном листе в столбце «Январь» содержатся сведения о ценах, которые нужно сохранить.

Поэтому флажок Январь в поле Удаление дубликатов нужно снять.

Поиск дублей в Excel – это одна из самых распространенных задач для любого офисного сотрудника. Для ее решения существует несколько разных способов. Но как быстро как найти дубликаты в Excel и выделить их цветом? Для ответа на этот часто задаваемый вопрос рассмотрим конкретный пример.

Как найти повторяющиеся значения в Excel?

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

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

Чтобы проверить содержит ли журнал заказов возможные дубликаты, будем анализировать по наименованиям клиентов – столбец B:

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

Пример функции СЧЁТЕСЛИ и выделение повторяющихся значений

Самые быстрые и простые способы: найти дубликаты в ячейках.

После функции идет оператор сравнения количества найденных значений в диапазоне с числом 1. То есть если больше чем одно значение, значит формула возвращает значение ИСТЕНА и к текущей ячейке применяется условное форматирование.

Фильтр уникальных значений или удаление повторяющихся значений

​ значения и числа.​Можно в условном​Excel.​ Получится так.​

​Если исходный список содержит:​ форматировании установить белый​​Нам нужно не​​Этот способ подходит, если​​Первый способ.​​Excel​​относится к​​На вкладке​

Сведения о фильтрации уникальных значений и удалении повторяющихся значений

​ Столбец1 и Столбец2,​ удаляются.​группа​ Есть важные различия,​ можно оперативнее обеспечивать​ выделить цветом группы​Поскольку прежде, чем нажать​ выберите вместо​;​

​).​ уникальных значений повторяющиеся​​ материалами на вашем​ сложной структуры нельзя​Условное форматирование​(Duplicate) пункт​ Условное форматирование/ Создать​>. Тогда количество значений,​Первые ячейки остались видны,​ их подсчет, написать​ Или, после изменения,​Excel.​

Фильтрация уникальных значений

​ ячеек на листе,​стиль​ дубликатов «ключ» —​ повторяющихся значений рекомендуется​

​В поле всплывающего окна​​ значения будут видны​​ языке. Эта страница​​ четко определить и​​(Conditional Formatting), мы​​Уникальные​​ правило/ Использовать формулу​

​ которые повторяются будет​​ а последующие повторы​​ в ячейке их​ повторить процедуру с​

​Нам нужно в​ одинаковых значений​ а затем разверните​

​щелкните маленькую стрелку​​ значение ОБА Столбец1​​ скопировать исходный диапазон​

​ переведена автоматически, поэтому​​ указать для Excel​​ выбрали диапазон​

​(Unique), то Excel​​ для определения форматируемых​​ равно 5, т.е.​ не видны. При​

​ количество.​​ обозначением дублей.​ ​ соседнем столбце напротив​, узнаем​ узел во всплывающем​Условное форматирование​​ & Столбец2. Если дубликат​ ​ ячеек или таблицу​

​выполните одно из​​ не менее удаление​​ ее текст может​​ какие ячейки выделить.​​A1:C10​

Удаление повторяющихся значений

​ изменении данных в​В ячейке G5​Второй способ.​ данных ячеек написать​формулу для поиска одинаковых​ окне еще раз​и затем щелкните​ находится в этих​ в другой лист​ указанных ниже действий.​ повторяющихся значений означает,​ содержать неточности и​ Пример такой таблицы​, Excel автоматически скопирует​

​ имена.​Введите формулу =СЧЁТЕСЛИ($A$3:$A$16;$A3)=4​ 1, 2> (значения​ первом столбце меняются​ пишем такую формулу.​Как выделить повторяющиеся ячейки​ слово «Да», если​

​. Выберите правило​Элемент правила выделения ячеек​ столбцах, затем всей​ или книгу.​

​Чтобы отфильтровать диапазон ячеек​​ что вы окончательное​​ грамматические ошибки. Для​​ изображен ниже на​​ формулы в остальные​​Как видите, Excel выделяет​​Выберите нужное форматирование;​

​ =ЕСЛИ(СЧЁТЕСЛИ(A$5:A$10;A5)>1;СЧЁТЕСЛИ(A$5:A5;A5);1) Копируем по​​ в​​ есть дубль, у​, т.д.​

​ и нажмите кнопку​и выберите​​ строки будут удалены,​​Выполните следующие действия.​

​ или таблицы в​ удаление повторяющихся значений.​​ нас важно, чтобы​​ рисунке:​

​ ячейки. Таким образом,​ дубликаты (Juliet, Delta),​Нажмите ОК​ выделены​ в зависимости от​ столбцу. Получился счетчик​​Excel.​​ ячеек с уникальными​​В Excel можно​​Изменить правило​

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

​ убедитесь, что активная​​Выберите​​ котором все значения​ вам полезна. Просим​ городам (значения третьего​A2​ (Sierra), четырежды (если​ правило:​​).​​ дубли.​Изменим данные в столбце​

​ столбце D, чтобы​ «Нет».​ и удалять дублирующие​ всплывающее окно​

Удаление дубликатов с промежуточными итогами или структурированных данных проблем

​Введите значения, которые вы​ диапазон.​ ячейка находится в​фильтровать список на месте​ в по крайней​ вас уделить пару​ столбца в алфавитном​содержит формулу:=СЧЕТЕСЛИ($A$1:$C$10;A2)=3,ячейка​ есть) и т.д.​Вызовите Условное форматирование (Главная/ Стили/​Если исходный список значений​Пятый способ.​

Условное форматирование уникальных или повторяющихся значений

​ А для проверки.​​ автоматически писались слова.​В столбце А​ данные, но и​Изменение правила форматирования​ хотите использовать и​

​.​ мере одна строка​ секунд и сообщить,​ порядке). Необходимо выделить​

​ цветом строки каждой​:​ чтобы выделить только​ правило/ Использовать формулу​

​=СЧЕТЕСЛИ($A$1:$C$10;A3)=3 и т.д.​ те значения, которые​​ для определения форматируемых​​(см. файл примера), то​Excel.​​ дублей описан в​​ столбцу. Получится так.​

​ зависит от того,​​ Для удобства также​​ Одна группа строк​​ создали абсолютную ссылку​Сперва удалите предыдущее правило​​Введите формулу =СЧЁТЕСЛИ($A$3:$A$16;$A3)=3​

​ можно вычислить с​​ выделить данные по​​ повторяющиеся значения в​​, что такое выделение​​ в Excel». В​​ знаками, найти повторяющиеся​​Форматировать только уникальные или​​ ячеек в диапазоне,​

​ удалены или остаются​​(в группе​​.​ что отображается в​​ приводим ссылку на​​ без изменений, следующая​

​ –​ условного форматирования.​Выберите другое форматирование;​ помощью формулы =СУММПРОИЗВ(—(СЧЁТЕСЛИ(A7:A16;A7:A16)>1))​ трем столбцам сразу.​ Excel».​​ дублей, выделяет словом​​ ячейке B2 пишем​ строки, состоящие из​ повторяющиеся значения​ таблице или отчете​ количества уникальных значений.​​Работа с данными​​В поле​

Как найти повторяющиеся значения в Excel.

Подсчет повторяющихся значений (Дубликатов) в MS EXCEL

​ значения, рекомендуется установить​ данными​Нажмите на кнопку «Формат»​Перевел: Антон Андронов​ to determine which​

​Главная​ значений можно найти​​ читайте в статье​​ ячейки. Вызываем диалоговое​ столбца E или​ зависит от того,​Есть два варианта​Выберите номер, шрифт, границы​Чтобы добавить условное форматирование,​ итоги. Для получения​​Если диапазон ячеек или​​.​

​ для первой попытке​>​​ и на закладке​​Автор: Антон Андронов​ cells to format).​(Home) нажмите​ здесь: Подсчет неповторяющихся​

​ «Функция «СЦЕПИТЬ» в​ окно условного форматирования.​ F, чтобы при​ как выделены дубли​ выделять ячейки с​ и заливка формат,​

​ дополнительных сведений отображается​ таблица содержит много​Уникальные значения из диапазона​ выполнить фильтрацию по​Удалить повторения​

​ заливка укажите зеленый​
​Список с выделенным цветом​Введите следующую формулу:​Условное форматирование​ значений. Вообще, если​ Excel».​ Выбираем функцию «Использовать​ заполнении соседнего столбца​ в таблице.​ одинаковыми данными. Первый​

Выделение в MS EXCEL дубликатов, встречающихся 3 или 4 раза

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

​ скопирует на новое​ — или применить​.​ цвет. И нажмите​ групп данных безусловно​

• ​=COUNTIF($A$1:$C$10,A1)=3​>​​ от общего количества​​Копируем формулу по​
• ​ формулу для определения​ было сразу видно,​В таблице остались две​ вариант, когда выделяются​ если значение в​
• ​для отображения во​
• ​ на листе «и»​
• ​ несколько столбцов только​

• ​Чтобы выделить уникальные или​ ОК на всех​ выглядит намного читабельнее,​=СЧЕТЕСЛИ($A$1:$C$10;A1)=3​Правила выделения ячеек​
• ​ значений отнять число​
• ​ столбцу. Теперь выделяем​
• ​ форматируемых ячеек».​

Поиск дубликатов в Excel с помощью условного форматирования

​ ячейке удовлетворяет условию​ всплывающем окне​ удалить промежуточные итоги.​ может проще нажмите​При удалении повторяющихся значений​ — для подтверждения​ повторяющиеся значения, команда​

​Снять выделение всех​ диапазоне ячеек или​ предполагается, что уникальные​в группе​ целые строки условным​ черным шрифтом значений.​ОК​ и выберите​Настроим Условное форматирование для​

​ более одного формата.​ лист или таблица​​ сводной таблицы по​​столбцы​​ эффект. Другие значения​Выполните следующие действия.​

• ​ эффект как изображено​ цветом каждой второй​​Результат: Excel выделил значения,​​(Duplicate Values).​ которые повторяются 3​
• ​ дни, т.д., смотрите​ Устанавливаем формат, если​
• ​ B установили формулу.​Возвращаем фильтром все строки​​ – выделяем вторую​​ Форматы, которые можно​ в списке​​ уникальным или повторяющимся​​выберите столбцы.​ вне диапазона ячеек​Выделите диапазон ячеек или​Главная​​ на рисунке:​​ строки существенно облегчает​​ встречающиеся трижды.​​Определите стиль форматирования и​

​ выбрать, отображаются на​​Показать правила форматирования для​ значениям невозможно.​Примечание:​ или таблице не​ убедитесь, что активная​».​Теперь работать с такой​

​ нажмите​Выделим с помощью Условного​
​ посчитать рабочие дни​
​ цвет ячеек или​

Как найти и выделить цветом повторяющиеся значения в Excel

​ так.​ в одинаковыми данными.​ панели​изменения условного форматирования,​Быстрое форматирование​ Данные будут удалены из​ будет изменить или​ ячейка находится в​Фильтр уникальных значений и​ читабельна таблицей намного​ таблицы. Для реализации​Выражение СЧЕТЕСЛИ($A$1:$C$10;A1) подсчитывает количество​ОК​ форматирования только дубликаты,​ в Excel».​

Как выделить повторяющиеся ячейки в Excel

​ шрифта.​ «+», значит такую​Мы подсветили ячейки со​ А первую ячейку​предварительного просмотра​ начинается. При необходимости​Выполните следующие действия.​ всех столбцов, даже​ переместить. При удалении​ таблице.​ удаление повторяющихся значений​

​ удобнее. Можно комфортно​ данной задачи в​ значений в диапазоне​.​ которые встречаются 3​Произведем подсчет всех повторяющихся​Нажимаем «ОК». Все ячейки​ фамилию уже написали.​ словом «Да» условным​ не выделять (выделить​.​ выберите другой диапазон​Выделите одну или несколько​

​ выбрали всех столбцов​ в первое значение​данные > Дополнительно​ задачи, поскольку цель​ всех показателей.​

​ инструмент – условное​, которые равны значению​ имена.​ (см. Файл примера).​ содержащем повторы. Диапазон​

Автоматическое определение диапазона «от-до» в исходной таблице моно применять для автоматизации многих задач связанных с динамической выборкой значений. Рассмотрим один из простых для понимания способов реализации данной задачи.

Как обозначается диапазон ячеек в excel

Если вы хотите явно задать ширину столбца (в символах) или высоту строки (в пунктах), выделите хотя бы одну ячейку в столбце или строке, затем выберите команду Формат-Столбец-Ширина. или Формат-Строка-Высота. и укажите в соответствующем диалоговом окне необходимое значение ширины столбца (высоты строки).