ЗАНЯТИЕ №4

Некоторые полезные возможности электронной таблицы Excel

1. Расчет встречаемости значения

Откроем файл пневмония.sav (м ожет называться по-английски и лежат в самых разных папках, нужно заранее посмотреть, где он лежит).

Выделить столбец с возрастом щелчком по имени столбца и скопировать в буфер обмена:

Вставить этот столбец в лист Excel.

Вставить две пустые строки в начало листа.

Вставить названия и рассчитать минимальные и максимальные значения:

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

- Щелкнуть по ячейке А3

- Нажать Shift и Ctrl одновременно и

- Щелкнуть по кнопке «стрелка вниз»

- Нажать Enter.

Аналогично рассчитываем количество непустых ячеек:

Далее начинаем рассчитывать встречаемость. Для этого, чтобы удобнее было заполнять большие таблицы по уже занятому месту, копируем столбец А в Е:

В ячейку Е1 вводим ссылку на минимальное значение

В ячейку Е3 вводим выражение, которое будет равно 1, если число в столбце А равно числу в Е1, и нулю иначе. Обращаем внимание на фиксацию строки и столбца:

Быстро копируем формулу вниз, используя выделение с Shift и Ctrl:

В ячейке Е2 суммируем то, что ниже,

т.е. ячейки с Е3 по Е1034:

Копируем колонку Е1:Е1034 в

F1:F1034

Меняем содержимое F1

на формулу: =Е1+1

Копируем колонку F1:F1034 вправо до столбца CG1:CG1034:

Вставляем еще одну строку вверх:

Рассчитываем долю больных данного возраста:

В ячейку E1 вводим =E3/$D$3.

Еще раз обращаем внимание на фиксацию ячеек:

Меняем формат на процентный:

Копируем вправо:

Строим частотную диаграмму встречаемости:

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

Копируем лист 1 щелчком по ярлыку и выполнением комады Переместить/Скопировать с отметкой «создать копию»:

Дать название листу - М

Вставляем столбец в начало:

Копируем из SPSS (файл - пневмония) столбец с полом:

Ставим курсор в А1 и трижды вставляем ячейки со сдвигом вниз, чтобы уравнять колонки с полом и возрастом:

Меняем формулу в F4, добавляя еще одно логическое условие, соединенное функцией И:

(в яч. «логич. выражение» вводим - И($B4=F$2;$A4=1), яч. «истина» и «ложь» без изменения)

Копируем на всю таблицу:

Копируем лист щелчком по ярлыку, создаем 3 копию и даем название новому листу Ж:

В ячейке F4 внесем изменения в формуле. Меняем 1 на 2 у $A4. Теперь в ячейке F4 формула выглядит так → ЕСЛИ (И($B4=F$2;$A4=2),:

В SPSS (пакет статистики) в файле пневмония 1 и 2 закодирован пол человека (1-это мужской пол, а 2-женский.)

На листе М было рассчитано сколько было мужчин данного возраста, а на листе Ж – рассчитаем сколько было женщин (в 3-ей строке отражено).

Копируем на всю таблицу:

Строим диаграмму, выбрав «Столбик на столбике» (гистограмма 2 вариант) на отдельном листе «диаграмма», выбирая 1-ый ряд F3:CH3 из листа М, второй ряд F3:CH3 из листа Ж.

Имена рядам даем м (3-я строка из листа «М») и ж (3-я строка из листа «Ж»):

1. Построение графика совместного распределения

Скопируем из файла пневмония два столбца: с частотой дыхания (rr) и температурой t на лист 2

Построим точечный график, используя их как значения осей X и Y соответственно:

Добавим третий столбец «умер»

Перестроим диаграмму с делением на выживших и умерших.

Для этого отсортируем в третьем столбце 0 от 1:

И построим точечную диаграмму, используя выживших и умерших как два отдельных ряда:

1. Использование условного форматирования

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

Щелкнем на пункт меню Analyze → из выпадающего списка выбираем Correlate → Bivariate.

Выделить все переменные в левой ячейки появившегося окна и, нажав кнопку со стрелкой, переместить их в правую ячейку. Нажать OK:

Скопируем рассчитанную матрицу корреляций, щелкнув в нее правой кнопкой мыши в буфер обмена. Вставим в Excel на новый лист:

Теперь нам надо выделить ячейки, в которых Sig менее 0,05. Для этого выделим щелчком четвертую строку:

Пункт меню Формат→Условное форматирование и задаем форматирование ячеек, значение в которых меньше 0,05:

Выделим строки от 6 до конца таблицы (обратит внимание, что лишние строки выделять нельзя, иначе вставит из буфера будет нельзя!):

Пункт меню Вставка (Правка ) → специальная вставка→форматы (или с помощью контекстного меню):

Результат:

4. Расчет динамики содержания лекарственного средства при дробном введении

Рассмотрим динамику содержания лекарственного средства при введении его шприцем в системный кровоток через равные промежутки времени.

Введем таблицу с начальными значениями

Вводим заголовки:

Вводим начальные данные:

Рассчитываем скорость вывода:

Добавим колонку «Вводимая доза» (начальное значение вводимой дозы - 0)

Рассчитываем время для следующего шага:

Если время с последнего введения меньше времени промежутка между введениями, то увеличиваем его на шаг по времени, если нет – даем значение, равное нулю. Для этого пользуемся функцией ЕСЛИ:

Рассчитываем новую массу с учетом того, что там может быть добавлена масса единомоментным введением:

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

Введенную дозу рассчитываем так: если время с введения увеличилась, то 0, если нет, то введенная доза:

Рассчитываем на 10 часов (6 тысяч шагов), копируя ячейки вниз до 6002 строки:

Строим график:

1. Расчет случайного блуждания

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

Разметим заголовки

Введем начальные значения и случайное число:

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

Пусть вариант движения 0 – влево, 1 – вверх, 2 – вправо и 3 – вниз.

Тогда при варианте движения 0 X должен быть отрицателен, а при 2 – положителен. Делаем это при помощи функции Если. Вначале при помощи мастера вызываем функцию и задаем первый вариант:

Потом в строке ввода формул выделяем и копируем функцию как текст (без знака =), и дописываем в конец выражения + и вставляем из буфера обмена скопированную формулу:

Меняем значения в формуле на =ЕСЛИ(D2=0; -1;0)+ЕСЛИ(D2= 2;1;0):

Копируем эту формулу, переходим в ячейку F1 и вставляем ее в сроку для ввода формул:

Корректируем формулу в ячейке F1 =ЕСЛИ(D2= 1; -1;0)+ЕСЛИ(D2= 3;1;0)::

Рассчитываем новое положение:

Копируем ячейку А2 вправо на В2:

Остальные ячейки во второй строке копируем вниз на 3-ью строку:

Выделяем блок ячеек А3: F3 и копируем его вниз на 1000 строк. По содержимому первых двух колонок строим точечную диаграмму:

1. Расчет случайного блуждания со случайным выбором шага и направления

Пусть теперь при блуждании направление выбирается произвольно, и при этом и шаг его – случайный равномерно распределенный на некотором промежутке. Дадим названия колонок и введем начальные значения:

Добавим два случайных числа:

Шаг получим умножением первого случайного числа на максимальный шаг:

Угол получим умножением второго случайного числа на 2пи:

Перемещение по Х задаем как длину шага на синус угла перемещения:

По второй оси Y – как длину шага на косинус угла перемещения:

Новое значение Х задаем как старое плюс перемещение:

Копируем эту ячейку вправо:

Остальные ячейки из второй строки копируем вниз на 3-ью строку:

Выделяем блок ячеек А3: I3 и копируем его вниз на 1000 строк. По содержимому двух колонок X и Y (А3: А1000 и I3: I1000) строим точечную диаграмму:

Пусть кроме блуждающей «мышки» есть также «кошка», двигающаяся в направлении мыши с шагом 1.

Зададим ее начальные движения

Рассчитаем разность по Х и по Y между мышкой и кошкой:

Расстояние между мышкой и кошкой

Рассчитываем направление движения кошки за мышью (dX и dY):

Рассчитываем новое положение кошки:

Формулу из J3 с помощью маркера заполнения копируем в К3:

Ячейки L2: P2 копируем вниз на 3-ью строку:

Выделяем блок ячеек J3: Р3 и копируем его вниз на 1000 строк

Добавим на предыдущий точечный график второй ряд, с положением кошки Х кошки и Y кошки (J2:J1000 и K2:K1000):

Скопируем диаграмму и укоротим ряды с 1001 до 101 точки:

Построим диаграмму расстояния между мышкой и кошкой

Если вдруг осталось время, усовершенствовать расчет: если в некоторый момент времени расстояние между мышью и кошкой менее 1, то кошка ловит мышь, и движение останавливается. Для этого вместо фиксированной максимальной длины прыжка мыши ввести колонку с текущей длиной и условием: если расстояние меньше 1, то прыжок равен 0.

Дата добавления: 2014-11-29