An introduction to non-linear data fitting using the Microsoft Excel

자료 적합은 심리물리학에서 연구자가 흔하게 마주치는 과정으로 상당한 노력과 시간이 든다. 자료 적합을 위한 상용 소프트웨어들이 많지만 처리 과정이 가려져 있거나 구입비용이 높다는 단점이 있다. 주변에서 쉽게 구할 수 있는 엑셀은 해 찾기 기능을 갖고 있으며 이를 이용해 복잡한 계산이 필요한 최적화를 수행할 수 있다. 엑셀은 스프레드시트 기반으로 자료 관찰과 그래프 그리기가 동시에 가능해 사용자가 자료 적합 과정을 직관적으로 쉽게 이해할 수 있다. 본 논문에서는 비선형 자료를 대상으로 엑셀에서 적합을 하는 과정을 단계별로 시 연한다. 이를 위해, 실제 자료에 심리측정 함수로서 심리학에서 널리 사용되는 로지스틱 함수가 적용되고 최소제곱 추정과 최대우도 추정으로 적합되는 과정이 차례대로 소개된다. 끝으로 자료 적합을 통해서 구해진 자료 분포의 파라미터를 이용해 어떻게 조건간 통계적 검증을 하는지가 설명된다.

keywords: data fitting, least squares estimation, maximum likelihood estimation, Psychometric functions, Excel solver, 자료 적합, 최소제곱추정, 최대우도추정, 심리측정함수, Excel solver

Abstract

Psychologists working on psychometric data often struggle with the process of data fitting which requires advanced knowledge about programming and mathematics. Although some commercial softwares reduce investigators effort to perform the process, still most of them are far from easy tools that a beginner tries them without hesitation. In contrast, the Microsoft Excel provides intuitive ways to fit data and calculate the parameters. In this paper, the processes of data fittings are demonstrated step by step using the Excel solver. Specifically, in fitting non-linear data, the least squares estimation and maximum likelihood estimation are introduced and the processes are compared to understand the difference. Finally, it was discussed how to statistically test the parameters between groups that were obtained from the data fitting.

keywords: data fitting, least squares estimation, maximum likelihood estimation, Psychometric functions, Excel solver, 자료 적합, 최소제곱추정, 최대우도추정, 심리측정함수, Excel solver

참고문헌

Afifi, A., May, S., & Clark, V. A. (2012). Practical multivariate analysis. Taylor & Francis Group:Florida.

Brown, A. M. (2001). A step-by-step guide to non-linear regression analysis of experimental data using a Microsoft Excel spreadsheet. Computer Methods in Biomedicine, 65, 191-200.

Brown, A. M. (2006). A non-linear regression analysis program for describing electrophysiological data with multiple functions using Microsoft Excel. Computer Methods and Programs in Biomedicine, 82, 51-57.

Cunningham, D. E., & Wallraven, C. (2012). Experimental Design: From user studies to Psychophysics. Taylor & Francis Group:Florida.

Dodson, C. S., Prinzmetal, W., & Shimamura, A. P. (1998). Using Excel to estimate parameters from observed data: An example from source memory data. Behavior Research Methods, Instruments, & Computers, 30(3), 517-526.

Dunn, J. C. (2010). How to fit models of recognition memory data using maximum likelihood. International Journal of Psychological Research, 3(1), 140-149.

John, E. G. (1998). Simplified curve fitting using spreadsheet add-ins. International Journal of Engineering Education, 14, 375-380.

Kemmer, G., & Keller, S. (2010). Nonlinear least-squares data fitting in Excel spreadsheets. Nature Protocol, 5, 267-281.

Kingdom, F. A. A., & Prins, N. (2010). Psychophysics: A practical introduction. Academic Press: London, UK.

10.

Lambert, R. J. W., Mytilinaios, I., Maitland, L., & Brown, A. M. (2012). Monte Carlo simulation of parameter confidence intervals for non-linear regression analysis of biological data using Microsoft Excel. Computer Methods and Programs in Biomedicine, 107, 155-163.

11.

Lasdon, L. S., Waren, A. D., Jain, A., & Ratner, M. (1978). Design and testing of a generalized reduced gradient code for nonlinear programming. ACM Transactions on Mathematical Software, 4(1), 34-50.

12.

Myung, I. J. (2003). Tutorial on maximum likelihood estimation. Journal of Mathematical Psychology, 47, 90-100.

13.

Stelmach, L. B., & Herdman, C. M. (1991). Directed attention and perception of temporal order. Journal of Experimental Psychology: Human Perception and Performance, 17(2), 539-550.

14.

Wichmann, F., & Hill, J. N. (2001). The psychometric function: Ⅰ. Fitting, sampling, and goodness fit. Perception & Psychophysics, 63(8), 1293-1313.

바로가기메뉴

논문 상세

Vol.34 No.3

엑셀을 이용한 비선형 자료 적합 소개

An introduction to non-linear data fitting using the Microsoft Excel

초록

Abstract

참고문헌

한국심리학회지: 일반